Call Sub from UserForm

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top