Help need on VB
i need an help from you guys,i am creating an data base file ,where people can enter data and later they can print out by queries and they can Update all the previous data...i have three user form for that,
userform 1 where people can enter data,which is working fine,
userform2 where people can queries and print out...which is not working
... userform 3 where people can update old data..which is not working as well...i am getting an error every time "permission Denied" friends ined help on this data base file please any help appericiated,
Thanks Advance for Help,
Userform 1 Code:
Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Database") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for eLog If Trim(Me.ComboBoxline.Value) = "" Then Me.ComboBoxline.SetFocus MsgBox "Please enter Data or Close the Form" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 2).Value = Me.ComboBoxline.Value ws.Cells(iRow, 3).Value = Me.DTPicker1.Value ws.Cells(iRow, 4).Value = Me.ComboBoxshift.Value ws.Cells(iRow, 5).Value = Me.ComboBoxarea.Value ws.Cells(iRow, 6).Value = Me.ComboBoxfeature.Value ws.Cells(iRow, 7).Value = Me.ComboBoxstop.Value ws.Cells(iRow, 8).Value = Me.txtfaultdescription.Value ws.Cells(iRow, 9).Value = Me.txtactionsteps.Value ws.Cells(iRow, 10).Value = Me.ComboBoxopenby.Value ws.Cells(iRow, 11).Value = Me.ComboBoxclosedby.Value ws.Cells(iRow, 12).Value = Me.ComboBoxjobstatus.Value ws.Cells(iRow, 13).Value = Me.DTPicker2.Value 'clear the data Me.ComboBoxline.Value = "" Me.DTPicker1.Value = "" Me.ComboBoxshift.Value = "" Me.ComboBoxarea.Value = "" Me.ComboBoxfeature.Value = "" Me.ComboBoxstop.Value = "" Me.txtfaultdescription.Value = "" Me.txtactionsteps.Value = "" Me.ComboBoxopenby.Value = "" Me.ComboBoxclosedby.Value = "" Me.ComboBoxjobstatus.Value = "" Me.DTPicker2.Value = "" Me.ComboBoxline.SetFocus End Sub Private Sub cmdClose_Click() Unload Me End Sub Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, _ ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date) End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "Please use the Exit button!" End If End Sub USerform 2 code:which i have not write the code... Private Sub CommandButton3_Click() Unload Me End Sub Private Sub CommandButton1_Click() date1 = Me.DTPicker1.Value date2 = Me.DTPicker2.Value With Sheets("Enter log") lr = .Cells(Rows.Count, "C").End(xlUp).Row With .Range("C2") .AutoFilter .AutoFilter Field:=2, Criteria1:=">=" & date1, Operator:=xlAnd _ , Criteria2:="<=" & date2 End With .Range("C2:C" & lr).EntireRow.Copy Sheets("reports").Range("A1") End With End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "Please use the Exit button!" End If End Sub 'userform :3 ....most important i need help on this code userform 3... 'i will atached the picture as well,...the error i am getting is " permission denied " Public str As String Private Sub ComboBox1_Change() Dim LastRow As Long Dim NextRow As Long Dim xval As Variant Dim i As Integer str = ComboBox1.Text With Worksheets("Data") Me.ComboBox2.Value = "" Me.ComboBox3.Value = "" Me.ComboBox4.Value = "" Me.ComboBox5.Value = "" Me.ComboBox6.Value = "" Me.ComboBox7.Value = "" Me.ComboBox8.Value = "" Me.ComboBox9.Value = "" Me.ComboBox10.Value = "" Me.ComboBox11.Value = "" NextRow = MatchRow If str = "1" Then For i = "" To "" xval = Range("b" & i).Value Me.ComboBox1.AddItem xval Next i ElseIf str = "2" Then For i = "" To "" xval = Range("b" & i).Value Me.ComboBox2.AddItem xval Next i ElseIf str = "3" Then For i = "" To "" xval = Range("b" & i).Value Me.ComboBox2.AddItem xval Next i ElseIf str = "offline1" Then For i = "" To "" xval = Range("b" & i).Value Me.ComboBox2.AddItem xval Next i ElseIf str = "offline2" Then For i = "" To "" xval = Range("b" & i).Value Me.ComboBox2.AddItem xval Next i ElseIf str = "offline3" Then For i = "" To "" xval = Range("b" & i).Value Me.ComboBox2.AddItem xval Next i End If End With End Sub Private Sub ComboBox2_Change() Dim LastRow As Long Dim NextRow As Long Dim ix As Integer ix = ComboBox2.ListIndex ix = ComboBox3.ListIndex ix = ComboBox4.ListIndex ix = ComboBox5.ListIndex ix = ComboBox6.ListIndex ix = ComboBox7.ListIndex ix = ComboBox8.ListIndex ix = ComboBox9.ListIndex ix = ComboBox10.ListIndex ix = ComboBox11.ListIndex With Worksheets("Data") NextRow = MatchRow If str = "1" Then Me.ComboBox7.Value = Cells(ix + 2, "").Value Me.ComboBox8.Value = Cells(ix + 2, "").Value Me.ComboBox9.Value = Cells(ix + 2, "").Value Me.ComboBox10.Value = Cells(ix + 2, "").Value Me.ComboBox11.Value = Cells(ix + 2, "").Value ElseIf str = "2" Then Me.ComboBox7.Value = Cells(ix + 6, "").Value Me.ComboBox8.Value = Cells(ix + 6, "").Value Me.ComboBox9.Value = Cells(ix + 6, "").Value Me.ComboBox10.Value = Cells(ix + 6, "").Value Me.ComboBox11.Value = Cells(ix + 6, "").Value ElseIf str = "3" Then Me.ComboBox7.Value = Cells(ix + 6, "").Value Me.ComboBox8.Value = Cells(ix + 6, "").Value Me.ComboBox9.Value = Cells(ix + 6, "").Value Me.ComboBox10.Value = Cells(ix + 6, "").Value Me.ComboBox11.Value = Cells(ix + 6, "").Value ElseIf str = "OFFLINE1" Then Me.ComboBox7.Value = Cells(ix + 6, "").Value Me.ComboBox8.Value = Cells(ix + 6, "").Value Me.ComboBox9.Value = Cells(ix + 6, "").Value Me.ComboBox10.Value = Cells(ix + 6, "").Value Me.ComboBox11.Value = Cells(ix + 6, "").Value ElseIf str = "OFFLINE2" Then Me.ComboBox7.Value = Cells(ix + 6, "").Value Me.ComboBox8.Value = Cells(ix + 6, "").Value Me.ComboBox9.Value = Cells(ix + 6, "").Value Me.ComboBox10.Value = Cells(ix + 6, "").Value Me.ComboBox11.Value = Cells(ix + 6, "").Value ElseIf str = "OFFLINE3" Then Me.ComboBox7.Value = Cells(ix + 6, "").Value Me.ComboBox8.Value = Cells(ix + 6, "").Value Me.ComboBox9.Value = Cells(ix + 6, "").Value Me.ComboBox10.Value = Cells(ix + 6, "").Value Me.ComboBox11.Value = Cells(ix + 6, "").Value ElseIf str = "UTILITIES" Then Me.ComboBox7.Value = Cells(ix + 6, "").Value Me.ComboBox8.Value = Cells(ix + 6, "").Value Me.ComboBox9.Value = Cells(ix + 6, "").Value Me.ComboBox10.Value = Cells(ix + 6, "").Value Me.ComboBox11.Value = Cells(ix + 6, "").Value End If End With End Sub Private Sub CommandButton1_Click() Dim LastRow As Long Dim NextRow As Long With Worksheets("Data") NextRow = MatchRow Cells(NextRow, "").Value = Me.ComboBox7.Value Cells(NextRow, "").Value = Me.ComboBox8.Value Cells(NextRow, "").Value = Me.ComboBox9.Value Cells(NextRow, "").Value = Me.ComboBox10.Value Cells(NextRow, "").Value = Me.ComboBox11.Value End With End Sub Private Sub CommandButton2_Click() Unload Me End Sub Private Sub UserForm_Activate() Dim LastRow As Long Dim coll As Collection Dim itm As Variant With Worksheets("Data") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set coll = New Collection On Error Resume Next For i = 2 To LastRow coll.Add .Cells(i, "").Value, CStr(.Cells(i, "").Value) Next i On Error GoTo 0 For Each itm In coll Me.ComboBox2.AddItem itm Next itm Me.ComboBox2.ColumnCount = 1 Me.ComboBox3.ColumnCount = 1 Me.ComboBox4.ColumnCount = 1 Me.ComboBox5.ColumnCount = 1 Me.ComboBox6.ColumnCount = 1 Me.ComboBox7.ColumnCount = 1 Me.ComboBox8.ColumnCount = 1 Me.ComboBox9.ColumnCount = 1 Me.ComboBox10.ColumnCount = 1 Me.ComboBox11.ColumnCount = 1 End With End Sub Private Function MatchRow() As Long On Error Resume Next MatchRow = Application.Match(Val(Me.ComboBox1.Value), Worksheets("Data").Columns(1), 0) MatchRow = Application.Match(Val(Me.ComboBox1.Value), Worksheets("Data").Columns(1), 3) If MatchRow > 0 Then Exit Function MatchRow = Application.Match(Me.ComboBox1.Value, Worksheets("Data").Columns(1), 0) MatchRow = Application.Match(Val(Me.ComboBox1.Value), Worksheets("Data").Columns(1), 3) On Error GoTo 0 End Function
It would help if you attach
It would help if you attach the Excel file to refer to.
hi, i don't know ,how to
hi,
i don't know ,how to attach an file here...
Thanks
Choose to edit your Forum
Choose to edit your Forum question. You should see 'File attachment' option.
Help need
sorry, i could not find the file attachment option, can i send you the file if possible,
thanks,
when you Edit your post, do
when you Edit your post, do you see this option?
"File attachments"
finally i got the place to
finally i got the place to upload ,but the file size is too large, about 12.5 MB,it can not be upload there,
Thanks,
can i send you the file ?
Thanks
strip out the bit that's
strip out the bit that's causing the problem.. otherwise, it might be a bit too big for a forum question, so you can Request a quote