Excel / VBA Consultancy

Free Training Videos

Free Spreadsheets

Excel / VBA Jobs

Navigation

New Excel Experts

milagroscanterbury63
shanelsosa9583420
tiffinybeaver135352

Current Excel / VBA Jobs

More jobs

Help need on VB

Submitted by tahir78 on 23 November, 2011 - 11:31

Question and Answer

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,

Login or register to post comments
ShareThis
7466 reads

It would help if you attach

Submitted by Vishesh on 25 November, 2011 - 17:03.

It would help if you attach the Excel file to refer to.

hi, i don't know ,how to

Submitted by tahir78 on 25 November, 2011 - 22:55.

hi,
i don't know ,how to attach an file here...
Thanks

Choose to edit your Forum

Submitted by Vishesh on 26 November, 2011 - 07:31.

Choose to edit your Forum question. You should see 'File attachment' option.

Help need

Submitted by tahir78 on 26 November, 2011 - 09:42.

sorry, i could not find the file attachment option, can i send you the file if possible,
thanks,

when you Edit your post, do

Submitted by Nick on 27 November, 2011 - 09:27.

when you Edit your post, do you see this option?
"File attachments"

finally i got the place to

Submitted by tahir78 on 27 November, 2011 - 16:19.

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

Submitted by Nick on 27 November, 2011 - 19:16.

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

Highest Ranked Users

User	Points
Nick	88347997
Vishesh	17313427
Almir	9042145
Vikas Verma	2380281
Aasim Shaikh	1995612
ammad134	1978846
bundy5150	1970720
HS710Excel	1970580
excelnovice1967	1968585
E5254730	1966830

Recent Blogs

Forums

hyperlink
5 years 36 weeks ago
BTL
6 years 22 weeks ago
How to adjust to reflect new BTL / tax landscape
6 years 34 weeks ago
myHyperlink Function
6 years 37 weeks ago
how much my house worth
6 years 38 weeks ago
Log an audit trail
6 years 43 weeks ago
fill cell base on color
6 years 52 weeks ago
Formula for Meeting Criteria
7 years 2 days ago
Question
7 years 3 days ago
Lock Data Cells
7 years 3 days ago

ExcelExperts.com