Call Sub from UserForm

ExcelNooberino

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

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

Forum statistics

Threads
1,084,962
Messages
5,380,841
Members
401,699
Latest member
ijazkhan01

Some videos you may like

This Week's Hot Topics

Top