Call Sub from UserForm

ExcelNooberino

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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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
28
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
28
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,102,630
Messages
5,488,000
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top