0

I am new to macro, but have some basic idea how it works or like able to write small VBA codes.

I have created a sample userform but i want to know how i can put values inside the code itself so that i dont want to maintain a separate lookup sheet to keep all those values for my dropdown option available under my USERFORM created.

Please find the code i used.

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboPart.ListIndex

'check for a part number
If Trim(Me.cboPart.Value) = "" Then
  Me.cboPart.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(lRow, 1).Value = Me.cboPart.Value
  .Cells(lRow, 2).Value = Me.cboPart.List(lPart, 1)
  .Cells(lRow, 3).Value = Me.cboLocation.Value
  .Cells(lRow, 4).Value = Me.txtDate.Value
  .Cells(lRow, 5).Value = Me.txtQty.Value
'  .Protect Password:="password"
End With

'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus

End Sub

Private Sub cmdClose_Click()
  Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close Form button!"
  End If
End Sub

Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cPart In ws.Range("PartIDList")
  With Me.cboPart
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
  End With
Next cPart

**For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    .AddItem cLoc.Value
  End With
Next cLoc**

Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus

End Sub

I learn how to set this userfrom from a web site and you can use this link to download the sample excel file (posted on that website)

Kindly help me on this.

Thanks in Advance

mithun nair
  • 136
  • 11
  • you are already setting a control value with this line `Me.cboPart.Value = ""` .... just use the same kind of code to set a value that is not blank – jsotola Oct 14 '17 at 19:28

1 Answers1

1

Lets say you want to add some predefined locations, in the UserForm_Initialize sub, instead of writing...

For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    .AddItem cLoc.Value
  End With
Next cLoc

...change it to

With Me.cboLocation
    .AddItem "Location 1"
    .AddItem "Location 2"
    .AddItem "Location 3"
    'Keep going as many as you like
End With
Rosetta
  • 2,665
  • 1
  • 13
  • 29
  • Thanks Rosetta, it worked well, will you help me one more thing, is it possible to use these values to save the excel file I have. If you can help me on this it will be great. – mithun nair Oct 15 '17 at 17:58
  • Sure. To put the selected location in the combo box into Excel spreadsheet range, say cell A1, is `Range("A1") = me.cboLocation.value` – Rosetta Oct 16 '17 at 02:16
  • Thanks, But the values we capture using a userform, how to get those value on a module we created, For example I select a value under 'cboLocation' from the UserFomr I created and I add a button, If user click on it which call MODULE 1 where the code is `SUB Try1 () Dim location Location = cbolocation MsgBox Location END SUB`. I tried this but I am getting null value under msgbox. – mithun nair Oct 18 '17 at 18:35
  • i think `Location = cboLocation.value` would solve the issue? – Rosetta Oct 19 '17 at 04:53
  • fyi there is difference between `.text` and `.value` for combobox, see https://stackoverflow.com/questions/2844193/distinction-between-using-text-and-value-in-vba-access – Rosetta Oct 19 '17 at 04:55
  • I tried both .VALUE and .TEXT `Sub Try1() Dim Location Location = cboLocation.Text MsgBox Location End Sub ` not working RUNTIME ERROR 424 OBJECT REQUIRED, any one have a solution for this ? – mithun nair Oct 20 '17 at 01:17
  • i suggest u start a new question as usually ppl dont follow extensive comments – Rosetta Oct 20 '17 at 11:47