Announcement

Collapse
No announcement yet.

add items with userforms sir

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • add items with userforms sir



    sir, i am click on the add item button add item user form was display, choose category compbo box1 show these options Antivirus, Cabinets, Cartridges,Smps, Desktop,Laptops, hard disk, keyboards, mouse, cctv, service chargies & other accessries,
    discrption of items combo box2 show items name,combo box3 show itmes hsn code, combo box4 show gst rates, gst rates pickup on the Gst Rate excel sheet, combo box 5 show unit name,than i am choose in catgory combo box 1 chose antivirus
    combo box 2 only show antivirus names only, compbo box 3 show only antivirus hsn code, combo box 4 show antivirus gst rate only, compbo box 5 show unit , i click on the add new item command button in add items userform, combo box 1 i am choose antivirus in category , combo box 2 i am add antivirus name & than combo box 3 auto show antivirus hsn code, combo box 4 auto show gst rate & combo box 5 auto show unit name & click on the save & clear command button the item was add on the antivirus list only sir, if i am add new item this was not in the items list in excel sheet combo box1 choose toner powder in category, combo box 2 add item name, combo box 3 add hsn code, combo box4 add hsn code, combo box 5 add unit name & click on the save & clear command button the item was add on the price list excel sheet sir, if i am chose combo box 1 antivirus in category, combo box 2 choose antivirus name & click on the delete item the item was delet on the price list excel sheet sir, see te attachments sir
    Attached Files

  • #2
    If you want to use data on a sheet to populate a userform or to do anything useful, it would be a good idea not to use merged cells
    Dave
    XLorate

    Comment


    • #3
      you can correct this sir & resolve this sir

      Comment


      • #4
        you can correct this sir resolve this sir

        Comment


        • #5
          .
          Here is most of the code. The remainder (very little) is in the project. You will need to ammend the code to fit your spreadsheet but everything you are seeking is there.

          Code:
          '---------------------------------------------------------------------------------------
          ' Module    : Database Form
          ' DateTime  : 31/08/2005 10:55. Updatede 08-02-08
          ' Author    : Roy Cox
          ' Purpose   : Data entry form for Excel, with Search facility
          ' Amended   : 27/04/2013
          '---------------------------------------------------------------------------------------
          Dim Ws As Worksheet
          Dim MyData As Range, c As Range, rFound As Range, rng As Range
          Dim r As Long
          Const frmMax As Long = 320
          Const frmHt As Long = 210
          Const frmWidth As Long = 290
          Dim oCtrl As MSForms.Control
          
          Private Sub cmbAdd_Click()
          'next empty cell in column A
          
              Set c = MyData.Cells(MyData.Rows.Count, 1).Offset(1)
          
              Application.ScreenUpdating = False    'speed up, hide task
              'write userform entries to database
              With Me
                  c.Value = .TextBox1.Value
                  c.Offset(0, 1).Value = .TextBox2.Value
                  c.Offset(0, 2).Value = .TextBox3.Value
                  c.Offset(0, 3).Value = .TextBox4.Value
                  If Me.optYes Then
                      c.Offset(0, 4).Value = "Yes"
                  ElseIf .optNo Then
                      c.Offset(0, 4).Value = "No"
                  End If
                  'clear the form
                  ClearControls
                  'resize database
                  Set MyData = c.CurrentRegion
                  Me.ScrollBar1.Max = MyData.Rows.Count
              End With
              Application.ScreenUpdating = True
          End Sub
          
          Private Sub cmbDelete_Click()
              Dim msgResponse As String    'confirm delete
              Application.ScreenUpdating = False
              'get user confirmation
              msgResponse = MsgBox("This will delete the selected record. Continue?", _
                                   vbCritical + vbYesNo, "Delete Entry")
              Select Case msgResponse    'action dependent on response
              Case vbYes
                 If c Is Nothing Then Set c = Ws.Cells(r, 1)
                  c.EntireRow.Delete    'remove entry by deleting row
                  Set MyData = Ws.Range("a8").CurrentRegion   'database
                  'restore form settings
                  With Me
                      .cmbAmend.Enabled = False    'prevent accidental use
                      .cmbDelete.Enabled = False    'prevent accidental use
                      .cmbAdd.Enabled = True    'restore use
                      .ScrollBar1.Max = MyData.Rows.Count
                      'clear form
                      ClearControls
                  End With
          
              Case vbNo
                  Exit Sub    'cancelled
              End Select
              Application.ScreenUpdating = True
          End Sub
          
          Private Sub cmbFind_Click()
              Dim strFind As String    'what to find
              Dim FirstAddress As String
              Dim f As Integer
          
          '    imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator
              strFind = Me.TextBox1.Value    'what to look for
          
              With MyData
              .AutoFilter
                  Set c = .Find(strFind, LookIn:=xlValues)
                  If Not c Is Nothing Then    'found it
          
                      With Me    'load entry to form
                          .TextBox2.Value = c.Offset(0, 1).Value
                          .TextBox3.Value = c.Offset(0, 2).Value
                          .TextBox4.Value = c.Offset(0, 3).Value
                          .cmbAmend.Enabled = True     'allow amendment or
                          .cmbDelete.Enabled = True    'allow record deletion
                          .cmbAdd.Enabled = False      'don't want to duplicate record
                          If c.Offset(0, 4).Value = "Yes" Then .optYes = True
                          If c.Offset(0, 4).Value = "No" Then .optYes = True
                          r = c.Row
                          f = 0
                      End With
                      FirstAddress = c.Address
                      Do
                          f = f + 1    'count number of matching records
                          Set c = .FindNext(c)
                      Loop While Not c Is Nothing And c.Address <> FirstAddress
                      If f > 1 Then
                          Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
          
                          Case vbOK
                              FindAll
                          Case vbCancel
                              'do nothing
                          End Select
                          Me.Height = frmMax
          
                      End If
                  Else: MsgBox strFind & " not listed"    'search failed
                  End If
              End With
          
          End Sub
          
          Private Sub cmbAmend_Click()
              Application.ScreenUpdating = False
              If r <= 0 Then Exit Sub
          
              Set c = Ws.Cells(r, 1)
              c.Value = Me.TextBox1.Value          ' write amendments to database
              c.Offset(0, 1).Value = Me.TextBox2.Value
              c.Offset(0, 2).Value = Me.TextBox3.Value
              c.Offset(0, 3).Value = Me.TextBox4.Value
              If Me.optYes Then
                  c.Offset(0, 4).Value = "Yes"
              ElseIf Me.optNo Then
                  c.Offset(0, 4).Value = "No"
              End If
              'restore Form
              With Me
                  .cmbAmend.Enabled = False
                  .cmbDelete.Enabled = False
                  .cmbAdd.Enabled = True
                  ClearControls
                  .Height = frmHt
              End With
              If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter
              Application.ScreenUpdating = True
              On Error GoTo 0
          End Sub
          Sub FindAll()
              Dim wesTemp As Worksheet
              Dim strFind As String    'what to find
          
              strFind = Me.TextBox1.Value
          
              If Not Ws.AutoFilterMode Then MyData.AutoFilter
          
              MyData.AutoFilter Field:=1, Criteria1:=strFind
          
              Me.ListBox1.Clear
              For Each c In MyData.Columns(1).SpecialCells(xlCellTypeVisible)
                  With ListBox1
                      .AddItem c.Value
                      .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
                      .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
                      .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
                      .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
                      .List(.ListCount - 1, 5) = c.Row
                  End With
              Next c
          
          End Sub
          Private Sub cmbLast_Click()
              Dim LastCl As Range
          
              With MyData
                  Set LastCl = .Cells(.Rows.Count, 1)
              End With
          
              With Me
                  .cmbAmend.Enabled = False
                  .cmbDelete.Enabled = False
                  .cmbAdd.Enabled = True
                  .TextBox1.Value = LastCl.Value
                  .TextBox2.Value = LastCl.Offset(0, 1).Value
                  .TextBox3.Value = LastCl.Offset(0, 2).Value
                  .TextBox4.Value = LastCl.Offset(0, 3).Value
                   If LastCl.Offset(0, 4).Value = "Yes" Then
                      .optYes = True
                  Else: .optNo = True
                  End If
              End With
          End Sub
          
          
          Private Sub cmnbFirst_Click()
              Dim FirstCl As Range
          
              'first data Entry
              Set FirstCl = MyData.Cells(2, 1)
          
              With Me
                  .cmbAmend.Enabled = False
                  .cmbDelete.Enabled = False
                  .cmbAdd.Enabled = True
                  .TextBox1.Value = FirstCl.Value
                  .TextBox2.Value = FirstCl.Offset(0, 1).Value
                  .TextBox3.Value = FirstCl.Offset(0, 2).Value
                  .TextBox4.Value = FirstCl.Offset(0, 3).Value
                  If FirstCl.Offset(0, 4).Value = "Yes" Then
                      .optYes = True
                  Else: .optNo = True
                  End If
              End With
          End Sub
          
          Private Sub ListBox1_Click()
          Set c = Nothing
              With Me.ListBox1
          
                  If .ListIndex = -1 Then    'not selected
                      MsgBox " No selection made"
                  ElseIf .ListIndex >= 1 Then    'User has selected
                      r = Val(.List(.ListIndex, .ColumnCount - 1))
                  End If
              End With
          
              With Me
                  .TextBox1.Value = .ListBox1.List(.ListBox1.ListIndex, 0)
                  .TextBox2.Value = .ListBox1.List(.ListBox1.ListIndex, 1)
                  .TextBox3.Value = .ListBox1.List(.ListBox1.ListIndex, 2)
                  .TextBox4.Value = .ListBox1.List(.ListBox1.ListIndex, 4)
                   r = .ListBox1.List(.ListBox1.ListIndex, 5)
                  .cmbAmend.Enabled = True      'allow amendment or
                  .cmbDelete.Enabled = True     'allow record deletion
                  .cmbAdd.Enabled = False       'don't want duplicate
                  If ListBox1.List(.ListBox1.ListIndex, 4) = "Yes" Then
                      .optYes = True
                  Else: .optNo = True
                  End If
              End With
          
          End Sub
          
          Private Sub ScrollBar1_Change()
          Dim Rw As Long
          Rw = Me.ScrollBar1.Value
          With Me
                  .cmbAmend.Enabled = False
                  .cmbDelete.Enabled = False
                  .cmbAdd.Enabled = True
                  .TextBox1.Value = MyData.Cells(Rw, 1).Value
                  .TextBox2.Value = MyData.Cells(Rw, 2).Value
                  .TextBox3.Value = MyData.Cells(Rw, 3).Value
                  .TextBox4.Value = MyData.Cells(Rw, 4).Value
                  If MyData.Cells(Rw, 5).Value = "Yes" Then
                      .optYes = True
                  Else: .optNo = True
                  End If
              End With
          
          End Sub
          
          Private Sub UserForm_Initialize()
          'change sheet name and Range here
              Set Ws = Sheet1
              Set MyData = Ws.Range("a8").CurrentRegion   'database
              With Me
                  .Caption = "Database Example"    'userform caption
                  .Height = frmHt
                  .Width = frmWidth
                  .ScrollBar1.Max = MyData.Rows.Count
                  .ScrollBar1.Min = 2
              End With
          End Sub
          
          Sub ClearControls()
              With Me
                  For Each oCtrl In .Controls
                      Select Case TypeName(oCtrl)
                      Case "TextBox": oCtrl.Value = Empty
                      Case "OptionButton": oCtrl.Value = False
                      End Select
                  Next oCtrl
              End With
          End Sub
          Attached Files

          Comment


          • #6
            you can create a vba code for last time posted excel sheet user form sir, i am resend the attachments sir
            Attached Files

            Comment


            • #7
              reply sir

              Comment


              • #8


                reply sir

                Comment

                Working...
                X