Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Thread Tools Display Modes
Old 12-03-2018, 12:34 AM
trevorc trevorc is offline Dynamic Userform multiselect listbox populate Windows 7 32bit Dynamic Userform multiselect listbox populate Office 2013
Competent Performer
Dynamic Userform multiselect listbox populate
Join Date: Jan 2017
Posts: 139
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default Dynamic Userform multiselect listbox populate

Hi All,
I have a doozy of a problm to solve.
I need to create a userform that has a dropdown list, this needs to be populated depending on the users selection from a work sheet that has different contract section amendments that can be selected to include or not.
once the user has selected the type of contract required I need the userform multi select listbox to be populated with selections based on the above.

So, first set dropdown selection

based on that populate multiselect listbox with options to select or not
Then use the selected items from the list to modify a word document to include selections or not.
I kept this as generic as posible as the main thing i need is help with the setup of the tasks, i can go from there once i have the basics for it all. There could be dozens of variations of the final Word ducument so i think the above is possibly the easiest way to limit the selection proccess and still provide a wide variation available.

The code below is what i have made so far just trying to get a handle on how to interact with the userform control.

Any advice or pointing in the right direction is always appriciated.

Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) _
As Long
Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) _
As Long
Private Sub CommandButton6_Click()
    Dim objFSO As New FileSystemObject
    Set myDIR = Application.FileDialog(msoFileDialogFolderPicker)
    With myDIR
        .Title = "Choose Directory"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        DIR_Selected = .SelectedItems(1)
    End With
    test_form_1.DIR_Name.Text = DIR_Selected    'The file path
    t = MsgBox("Select Yes to Enter a File name or use the system generated one ?", vbYesNo)
    If t = 6 Then
    test_form_1.Quote_Name.Text = "Arbor Quote " & Format(Date, "dd-mm-yy")
        test_form_1.Quote_Name.Text = InputBox("Enter the file name to use for the Word Document", vbOKOnly)    'The file name
    End If
End Sub
Private Sub Create_Quote_CMD_Click()
' Maim Code goes here...
End Sub
Private Sub OptionButton6_Click()
test_form_1.Schedule_Selecion.Value = test_form_1.OptionButton1.Caption
End Sub
Private Sub OptionButton7_Click()
test_form_1.Schedule_Selecion.Value = test_form_1.OptionButton2.Caption
End Sub
Private Sub OptionButton8_Click()
test_form_1.Schedule_Selecion.Value = test_form_1.OptionButton8.Caption
End Sub
Private Sub OptionButton9_Click()
test_form_1.Schedule_Selecion.Value = test_form_1.OptionButton9.Caption
End Sub
Private Sub Schedule_Selecion_Change()
    Select Case Schedule_Selecion
    Case "Schedule 1"
        test_form_1.OptionButton6.Value = True
    Case "Schedule 2"
        test_form_1.OptionButton7.Value = True
    Case "Schedule 3"
        test_form_1.OptionButton8.Value = True
    Case "Schedule 4"
        test_form_1.OptionButton9.Value = True
    End Select
End Sub
Private Sub UserForm_Initialize()
    test_form_1.Contract_Name.Text = ""
    test_form_1.DIR_Name.Text = ""
    Schedule_Selecion.AddItem "Schedule 1"
    Schedule_Selecion.AddItem "Schedule 2"
    Schedule_Selecion.AddItem "Schedule 3"
    Schedule_Selecion.AddItem "Schedule 4"
    Schedule_Selecion.Text = "Schedule 1"
    test_form_1.OptionButton6 = True
End Sub
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
        Create_Quote_CMD.Enabled = True
        Create_Quote_CMD.Enabled = False
    End If
End Sub
Private Sub ComboBox1_Change()
Dim rngColor As Range
Dim ws As Worksheet
Set ws = Worksheets("Form Options")
For Each rngColor In ws.Range("$B#5:$B9")
Me.Schedule_Selection.AddItem rngColor.Value
Next rngColor
End Sub
Private Sub CommandButton1_Click()
   Dim oCtrl As Control
    For Each oCtrl In Frame1.Controls
        If TypeName(oCtrl) = "OptionButton" Then
            If oCtrl.Value = True Then
                t = oCtrl.Caption
                Exit For
            End If
        End If
Call CommandBut_Click
End Sub
Private Sub CommandButton3_Click()
    With ActiveSheet
        .Shapes("Rectangle 2").Visible = True
    End With
Unload Me
End Sub
Private Sub OK_Click()
Unload Me
End Sub
Private Sub CommandButton5_Click()
End Sub
Sub Macro23()
End Sub
Sub macro24()
End Sub
Sub CommandBut_Click()
    Dim PageNumber As Integer
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim FileToOpen As String
    Dim strPath As String
    FileToOpen = "test.docx"
    strPath = "C:\Contracts\"
    'the next line looks to a cell to decide what page number to scroll to
    PageNumber = Cells(2, 7).Value
    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    If wrdApp Is Nothing Then
        Set wrdApp = CreateObject("Word.Application")
        Set wrdDoc = wrdApp.Documents.Open(strPath & FileToOpen)
        On Error GoTo notOpen
        Set wrdDoc = wrdApp.Documents(FileToOpen)
        GoTo OpenAlready
        Set wrdDoc = wrdApp.Documents.Open(strPath & FileToOpen)
    End If
    On Error GoTo 0
    With wrdApp
        .Visible = True
        B_Count = .ActiveDocument.Bookmarks.Count
        Dim bmk As Bookmark
        Dim msg As String
        For Each bmk In ActiveDocument.Range.Bookmarks
            msg = msg & bmk.Name & vbCr
        Next bmk
        If B_Count > 1 Then
            For I = 1 To B_Count
                a = B_Count
                b = .ActiveDocument.Bookmarks
            Next I
        End If
        ' Set rngStart = ActiveDocument.Bookmarks("Start").Range
        ' Set rngEnd = ActiveDocument.Bookmarks("End").Range
        ' ActiveDocument.Range(rngStart.Start, rngEnd.End).Select
        ' ActiveDocument.Range(rngStart.Start, rngEnd.End).Delete
        '.Selection.Goto What:=1, Which:=2, Name:=PageNumber
    End With
    yy = ((test_form_1.DIR_Name.Text) & test_form_1.Contract_Name.Text)
    wrdDoc.SaveAs (test_form_1.Contract_Name.Text)
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub
Reply With Quote
Old 12-03-2018, 02:49 PM
NoSparks NoSparks is offline Dynamic Userform multiselect listbox populate Windows 7 64bit Dynamic Userform multiselect listbox populate Office 2010 64bit
Excel Hobbyist
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 804
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough


Last edited by NoSparks; 12-03-2018 at 09:07 PM.
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiselect listbox - custom outlook form A_Balt Outlook 0 07-19-2018 09:36 AM
Dynamic Userform multiselect listbox populate Moving Selected Items from a Multiselect Listbox on a userform to a bookmark in Word marksm33 Word VBA 3 01-15-2015 07:55 PM
This is a Userform LIstbox queston: A variable does not set to the value of a listbox CatMan Excel Programming 14 08-18-2014 08:14 PM
Dynamic Userform multiselect listbox populate This is a Userform LIstbox queston: A variable does not set to the value of a listbox CatMan Excel 1 08-08-2014 09:41 AM
Data from Excel to Listbox multiselect to Word marksm33 Word VBA 2 02-18-2014 08:30 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:35 PM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd. is not affiliated with Microsoft