Listbox not working when called from a command button

[Dedeke]

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:

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:

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

 

[Fluff]

In that case I don't know, there is nothing wrong with the code & it works for me regardless of which sheet is active.

 

[Dedeke]

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.

 

[Dedeke]

This one works

  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

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Dedeke]

Glad you sorted it & thanks for the feedback.

No problem, i'm glad getting so much help over here.

A new one is comming up.
will start a new post