Results 1 to 6 of 6

Thread: Call Sub from UserForm

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Call Sub from UserForm

    Hello guys!

    I have this UserForm I've created and it has some ListBoxes where I am .Adressing the range from a different sheet, the thing is, it works just fine if I just want to show one unique range all the time. It happens that I need it to show different sheet ranges when I click different buttons that I've created on the main sheet. For example, this is what I have on the UserForm code:

    Code:
    Private Sub UserForm_Initialize()
    
    Application.ScreenUpdating = False
    
    
    Dim LastRow As Long
    Dim wb As Workbook
    Dim wsTemp As Worksheet
    
    
    Set wb = ThisWorkbook
    Set wsTemp = wb.Sheets("VW428")
    
    
    LastRow = Sheet2.Range("K" & Rows.Count).End(xlUp).row
    
    
    wb.Sheets("VW428").Activate
    
    
    With ListBox1
    .ColumnCount = 3
    .ColumnWidths = "73;230;25"
    .RowSource = wsTemp.Range("I5:K" & LastRow).Address
    End With
    
    
    With ListBox2
    .RowSource = wsTemp.Range("A1").Address
    End With
    
    
    wb.Sheets("Dashboard").Activate
    
    
    Application.ScreenUpdating = True
    
    
    End Sub
    With this it perfectly shows the range that I'm adressing but is there a way I can create differente "Initializes" for the same UserForm in a way that I can call them from different buttons?! Thanks in advance!

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Call Sub from UserForm

    Could you not add a combobox to your Userform to show various ranges like 1 to 4, then add something like the following code to select those ranges when the userform is showing ??
    Code:
    Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value
    Case 1: ListBox1.RowSource = Range("a1:a12").Address
    Case 2: ListBox1.RowSource = Range("b1:b12").Address
    Case 3: ListBox1.RowSource = Range("c1:c12").Address
    Case 4: ListBox1.RowSource = Range("d1:d12").Address
    End Select
    End Sub

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Call Sub from UserForm

    Hello again!

    I've managed to do it without needing the combobox, I just created a bunch of buttons and assign different sub's to each one of them. However, I'm getting a stupid bug each time I press a button. Like, for example, if I press Button A it shows me the data from the project A I want to see, but if after that I press a different button, lets assume Button B, it still shows me the data from the Button A. I need to close the userform, and press the Button B a second time for the userform show me the correct data from the project B. Is there any way I can counter this!?

    Thanks in advance! :D
    Last edited by ExcelNooberino; Aug 23rd, 2019 at 06:58 AM.

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Call Sub from UserForm

    With 4 Commandbuttons in the Userform/module this worked for me.
    If you are still stuck please send your code !!!
    Code:
    Private Sub CommandButton1_Click()
    ListBox1.RowSource = Range("a1:d11").Address
    End Sub
    
    Private Sub CommandButton2_Click()
    ListBox1.RowSource = Range("a13:d23").Address
    End Sub
    
    Private Sub CommandButton3_Click()
    ListBox1.RowSource = Range("a25:d35").Address
    End Sub
    
    Private Sub CommandButton4_Click()
    ListBox1.RowSource = Range("a37:d45").Address
    End Sub

  5. #5
    New Member
    Join Date
    Jan 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Call Sub from UserForm

    The code below is the subs for two of the buttons I have:

    Code:
    Public Sub ShowSharanCriticalDetails()
    
    Application.ScreenUpdating = False
    
    
    detailsForm.Show
    
    
    Dim LastRow As Long
    Dim wb As Workbook
    Dim wsTemp As Worksheet
    
    
    Set wb = ThisWorkbook
    Set wsTemp = wb.Sheets("VW428")
    
    
    LastRow = Sheet2.Range("K" & Rows.Count).End(xlUp).row
    
    
    wb.Sheets("VW428").Activate
    
    
    If wsTemp.Range("I5").Text = "" Then
    
    
    With detailsForm.ListBox1
    .ColumnCount = 3
    .ColumnWidths = "74;230;25"
    .RowSource = wsTemp.Range("Q1:S1").Address
    End With
    
    
    With detailsForm.ListBox2
    .RowSource = wsTemp.Range("A1").Address
    End With
    
    
    Else
    
    
    With detailsForm.ListBox1
    .ColumnCount = 3
    .ColumnWidths = "74;230;25"
    .RowSource = wsTemp.Range("I5:K" & LastRow).Address
    End With
    
    
    With detailsForm.ListBox2
    .RowSource = wsTemp.Range("A1").Address
    End With
    
    
    End If
    
    
    wb.Sheets("Dashboard").Activate
    
    
    Application.ScreenUpdating = True
    
    
    End Sub
    Public Sub ShowSharanBelowTargetDetails()
    
    
    Application.ScreenUpdating = False
    
    
    detailsForm.Show
    
    
    Dim LastRow As Long
    Dim wb As Workbook
    Dim wsTemp As Worksheet
    
    
    Set wb = ThisWorkbook
    Set wsTemp = wb.Sheets("VW428")
    
    
    LastRow = Sheet2.Range("O" & Rows.Count).End(xlUp).row
    
    
    wb.Sheets("VW428").Activate
    
    
    If wsTemp.Range("M5").Text = "" Then
    
    
    With detailsForm.ListBox1
    .ColumnCount = 3
    .ColumnWidths = "74;230;25"
    .RowSource = wsTemp.Range("Q1:S1").Address
    End With
    
    
    With detailsForm.ListBox2
    .RowSource = wsTemp.Range("A1").Address
    End With
    
    
    Else
    
    
    With detailsForm.ListBox1
    .ColumnCount = 3
    .ColumnWidths = "74;230;25"
    .RowSource = wsTemp.Range("M5:O" & LastRow).Address
    End With
    
    
    With detailsForm.ListBox2
    .RowSource = wsTemp.Range("A1").Address
    End With
    
    
    End If
    
    
    wb.Sheets("Dashboard").Activate
    
    
    Application.ScreenUpdating = True
    
    
    End Sub

  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Call Sub from UserForm

    It appears your trying to load the userform listbox from a button on the sheet. Why have you not got the buttons on the userform
    or load the listboxes From a dropdown on the userform.

    In a basic case the listbox could be initially loaded in a Userform Initialization event,
    then to stop reloading the userform you could use button on the userform to load the other ranges.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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