Help need on VB

Hello all my Experts friends,

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
Thanks Advance for your Help,
Userform123
Vishesh's picture

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

Vishesh's picture

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,

Nick's picture

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

Nick's picture

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