Call Sub from UserForm

ExcelNooberino

New Member
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
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
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
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
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
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

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top