Listbox not working when called from a command button

Dedeke

Board Regular
Joined
Dec 1, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Having the following problem.
I try opening a form with a simple listbox on it by a command button.
When opening from the sheet "data" with cmdbtn it works fine (Form on data )
When opening the same Form from the sheet "Start" also with a command button (Form on Start) the listbox won't work.
Where do i go wrong, by the way I've got so far by playing arround with vba found here or on the internet.

Using this code in the Form:
VBA Code:
Private Sub ListBox1_Click()
Dim ws As Worksheet
Set ws = Sheets("Data")
Dim sRow As Long
    If Me.ListBox1.ListIndex > -1 Then
        sRow = Me.ListBox1.ListIndex + 1
    End If
    Me.TextBox7.Text = Cells(sRow, 1) 'value from Column 5 (E)
    Me.TextBox8.Text = Cells(sRow, 2)
    Me.TextBox9.Text = Cells(sRow, 3)
    Me.TextBox10.Text = Cells(sRow, 4)
End Sub

Private Sub UserForm_Activate()
Dim ws As Worksheet
Set ws = Sheets("Data")
Dim vCol As Variant
Dim Lrow As Long
    Lrow = Sheets("Data").UsedRange.Rows(Sheets("Data").UsedRange.Rows.Count).Row
    vCol = Sheets("Data").Range("A1:A" & Lrow).Value
    Me.ListBox1.List = vCol
     
End Sub

Private Sub Userform_Initialize()
Dim ws As Worksheet
Set ws = Sheets("Data")

Dim vCol As Variant
Dim Lrow As Long
   Lrow = Sheets("Data").UsedRange.Rows(Sheets("Data").UsedRange.Rows.Count).Row
   vCol = Sheets("Data").Range("A1:A" & Lrow).Value
    Me.ListBox1.List = vCol
End Sub

This is de code on both the command buttons:
Code:
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
 

Attachments

  • Form on data.JPG
    Form on data.JPG
    71.6 KB · Views: 0
  • Form on start.JPG
    Form on start.JPG
    57.3 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,623
Office Version
  1. 365
Platform
  1. Windows
In that case I don't know, there is nothing wrong with the code & it works for me regardless of which sheet is active.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Dedeke

Board Regular
Joined
Dec 1, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
In that case I don't know, there is nothing wrong with the code & it works for me regardless of which sheet is active.
Ok Thx Fluff for looking into it.
I'll keep seaching.
 
Upvote 0

Dedeke

Board Regular
Joined
Dec 1, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
This one works

VBA Code:
  Sub MyRenameSheets1()

    Dim Lrow As Long
    Dim r As Long
    Dim prevNm As String
    Dim newNm As String
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Set ws = Sheets("Data")
'   Find last row in column A on Summary sheet with data
    Lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
  
'   Loop through all rows on Summary sheet starting on row 2
    For r = 2 To Lrow
'       Capture values
        prevNm = ws.Cells(r, 1)
        newNm = ws.Cells(r, 5)
'       Rename sheets
        If newNm <> Empty Then
            
            On Error Resume Next
            Sheets(prevNm).name = newNm
                If Err.Number > 0 Then                   
                    MsgBox "Error found: " & Err.Description
                    
                Else                   
                    If prevNm <> newNm Then ws.Cells(r, 1) = newNm
                
                End If
            On Error GoTo 0
        End If
    Next r
    ws.Range("E2:E12" & Lrow).ClearContents
    Application.ScreenUpdating = True
End Sub
So Fluff, your solution worked actually fine. In the "Data" sheet was a hidden column left behind with caused the proplem.

Till next time
 
Upvote 0
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,623
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,186,647
Messages
5,958,973
Members
438,388
Latest member
wl4437

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
Top