Call Sub from UserForm

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
24
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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
24
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:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
24
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
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,988
Messages
5,484,056
Members
407,426
Latest member
Owen Chia

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top