Consulting

Results 1 to 15 of 15

Thread: help with vba code in userform

  1. #1

    help with vba code in userform

    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.
    Attached Files Attached Files

  2. #2
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    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.
    Attached Files Attached Files
    Last edited by chubbychub; 02-14-2019 at 02:16 PM.

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

  5. #5

    Post Userform Intialize selecting listbox to textbox

    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

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  7. #7
    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

  8. #8
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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?
    Greg

    Visit my website: http://gregmaxey.com

  9. #9
    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

  10. #10
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  11. #11
    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


  12. #12
    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



  13. #13
    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

  14. #14
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Steve, sorry for not getting back to you and for any confusion. Yes, that is what I was trying to show you.
    Greg

    Visit my website: http://gregmaxey.com

  15. #15
    That's alright Greg,

    I just realised that i had to change to activate!

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

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •