help with vba code in userform

[chubbychub]

hi, I am new to vba macro in word, I have followed guides and tutorials and I can t seem to get it to work. I just need 4 fields in the userform fill and entered when I click on the 'command button' . Please help as I have no clue why it has errors (it just highlight userform vba editor) and not sure how I can accomplish this. Please see attachments and if possible saved me from this endless journey. Thanks in advanced.

[gmayor]

Where does one start? Your userform is called 'contractor' and not Userform1 so it is never called and produces an error. The userform has no action button to write the values from the texts boxes, which are in any case duplicated with overlaid text boxes. Your macros have typos in the VBA commands. VBA is very strict on spelling accuracy. The bookmark you are attempting to write to doesn't exist. The blue button is superfluous unless you want to use it to call the userform again, in which case the command associated with it should be

Option Explicit

Private Sub Document_Open()
Contractor.Show
End Sub

Private Sub Process_Click()
Contractor.Show
End Sub


You need to add a pair of command buttons to the userform and then the code in that form is (for one text box).


Option Explicit

Private Sub CommandButton1_Click()
Dim Contractor As Range
On Error Resume Next
Set Contractor = ActiveDocument.Bookmarks("contractor").Range
Contractor.Text = TextBox1.Value
ActiveDocument.Bookmarks.Add "contractor", Contractor
Unload Me
Set Contractor = Nothing
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub


See http://www.gmayor.com/Userform.htm which covers the basics of userforms.

[chubbychub]

thank you Gmayor! I changed the code to what you posted. How do I make it to enter the date in the pages below (in red) name, owner, date, and amount. just those 4 fields. thanks in advanced.Please see attachments.
I created comman button click2 bt don't know how it can cpurethe dta and enter it into hosefields.

[chubbychub]

Thank you Gmayor! I think I got it from your tutorials. Mark as solve. Thank you very much!. This is very helpful.

[stevenbehr]

hi,

I have 10 listboxes in my userform and I can double click each listbox lines and populate information from there to 10 textboxes.

Im wondering if I can in Userform Intialize, once the userform opens that the inifromation in listboxes be populated automatically to the textboxes.

I have a code below but doesn't do anything really just selects the first list but no double click

lstLookup.Value = Selection
For i = 1 To lstLookup.ListCount - 1
lstLookup.Selected(i) = True

Next I

End Sub

Thanks

Steve

[gmaxey]

Not sure what you are trying to do, but this might get you started:

Private Sub UserForm_Initialize()
Dim arrItems() As String
Dim bRB As MSForms.ReturnBoolean
arrItems = Split("Apples|Oranges|Peaches|Pears", "|")
ListBox1.List = arrItems
ListBox1.Selected(2) = True
ListBox1_DblClick bRB
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim lngIndex As Long
For lngIndex = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngIndex) Then
TextBox1.Text = ListBox1.List(lngIndex)
Exit For
End If
Next lngIndex
End Sub

Note the CANCEL will not function in this case.

[stevenbehr]

Your almost there Maxey,
I should have included my list box Dblclick

The code for my list box is:

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
Dim ID As String
Dim i As Integer
Dim findvalue '
'error block
On Error GoTo errHandler:
'get the select value from the listbox
For i = 0 To lstLookup.ListCount - 1
If lstLookup.Selected(i) = True Then
'set the listbox column
ID = lstLookup.List(i, 6)
End If
Next i
'find the value in the range
Set findvalue = Sheet12.Range("L:L").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)
'add the values to the userform controls
cNum = 6
For X = 1 To cNum
Me.Controls("Reg" & X).Value = findvalue
Set findvalue = findvalue.Offset(0, 1)
Next
'disable the controls to make the user select an option
'Me.cmdAdd.Enabled = False
'Me.cmdAdd.BackColor = RGB(220, 220, 220)
'Me.cmdEdit.Enabled = False
'Me.cmdEdit.BackColor = RGB(220, 220, 220)
'Me.optAdd = False
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub

[gmaxey]

What else do you need? I've shown you how to call the dbl_click event from the initialize event. It doesn't appear that you need the Cancel Property of the event so what have I missed?

[stevenbehr]

Hi Greg,

Run-time error 70

Permission denied.

Can you tell me what the arr items
Apples|Oranges|Peaches|Pears is. Im unaware of the string arr.

Private Sub UserForm_Initialize()

Dim arrItems() As String
Dim bRB As MSForms.ReturnBoolean
arrItems = Split("Apples|Oranges|Peaches|Pears", "|")
lstLookup.List = arrItems
lstLookup.Selected(2) = True
lstLookup_DblClick bRB



End Sub

[gmaxey]

I was just providing an example of how to populate a listbox from the initialize event, select and item and then call the listbox dbl_click event from the initialize procedure. You certainly don't need the bit of code dealing with apples and oranges so you can stet out these lines as I assume you already have code to populate your listboxes and select the item:

arrItems = Split("Apples|Oranges|Peaches|Pears", "|")
lstLookup.List = arrItems
lstLookup.Selected(2) = True

[stevenbehr]

Hi Greg,

Its still indicates permission denied.

I do believe that you got the code below correct


lstLookup.Selected(2) = True
lstLookup_DblClick bRB

But do I cut out

Dim arrItems() As String
arrItems = Split("Apples|Oranges|Peaches|Pears", "|")

Either combination I still get permission denied.

Furthermore, in your 1st reply you added
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim lngIndex As Long
For lngIndex = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngIndex) Then
TextBox1.Text = ListBox1.List(lngIndex)
Exit For
End If
Next lngIndex
End Sub


Does this mean that I need to add that to my listbox code to make this work?

Sorry that I am a bit of a pain as this coding for initialization is new to me!

Steve

[stevenbehr]

Hi Greg,

Heres another solution.

What about changing my
lstLookup_DblClick to change or beforeupdate in order for Userform initialize to take control of the code below

'declare the variables
Dim ID As String
Dim i As Integer
Dim findvalue '
'error block
On Error GoTo errHandler:
'get the select value from the listbox
For i = 0 To lstLookup.ListCount - 1
If lstLookup.Selected(i) = True Then
'set the listbox column
ID = lstLookup.List(i, 6)
End If
Next i
'find the value in the range
Set findvalue = Sheet12.Range("L:L").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)
'add the values to the userform controls
cNum = 6
For X = 1 To cNum
Me.Controls("Reg" & X).Value = findvalue
Set findvalue = findvalue.Offset(0, 1)
Next
'disable the controls to make the user select an option
'Me.cmdAdd.Enabled = False
'Me.cmdAdd.BackColor = RGB(220, 220, 220)
'Me.cmdEdit.Enabled = False
'Me.cmdEdit.BackColor = RGB(220, 220, 220)
'Me.optAdd = False
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub

[stevenbehr]

Hui Greg,

I FOUND IT!

Private Sub UserForm_Activate()
'Dim arrItems() As String
Dim bRB As MSForms.ReturnBoolean
lstLookup.Selected(0) = True
lstLookup_DblClick bRB
End Sub

[gmaxey]

Steve, sorry for not getting back to you and for any confusion. Yes, that is what I was trying to show you.

[stevenbehr]

That's alright Greg,

I just realised that i had to change to activate!

Thanks for the tip ....you learn something everyday!

Steve