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

 

[Dedeke]

You need to qualify the Cells on this line as well

Lrow = Cells(Rows.Count, "E").End(xlUp).Row

sorry Fluff, don't get it
Do you mean like this

Lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Cause this does not work

 

[Fluff]

Yes, that is what I meant.

In what way doesn't it work?

 

[Dedeke]

Yes, that is what I meant.

In what way doesn't it work?

It has to run with command button from "Start" sheet.
Actually the same way as the form in the first problem.
So the code is working perfectly when run from commandbtn on sheet"Data" but not with cmdbtn on sheet"Start"
Changed the code as following

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

Still works from "Data" but not from "Start"

 

[Dedeke]

It has to run with command button from "Start" sheet.
Actually the same way as the form in the first problem.
So the code is working perfectly when run from commandbtn on sheet"Data" but not with cmdbtn on sheet"Start"
Changed the code as following

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

Still works from "Data" but not from "Start"

Can i upload a reduced file or only with the tool "Upload Mini sheet" ???

 

[Fluff]

In what way doesn't it work?

 

[Dedeke]

In what way doesn't it work?

 

[Fluff]

Can you please explain, in words, what is not working.

 

[Dedeke]

Can you please explain, in words, what is not working.

Try my best English
For info: This sub is going to be integrated in the form wich you treaded first.

Before doiing this i always try if the sub works separtly.

So, the sub changes everything as needed when i run it from the sheet "Data" (See From Data1 jpeg)
I made a cmd button calling the same sub on the sheet"Start" (See FromStart.jpeg)
So for testing i First put some new names in the colum "newnames" on sheet "Data"
Then i switch to the sheet"Start"
Push the button to run the sub.
The only thing what happens is the last line from the sub: clearing the range F2:F12 on the sheet "Data".
Further on no changes made.
Hope this can help you a bit more

 

[Fluff]

Do you get any errors?

 

[Dedeke]

Do you get any errors?

Not a single one, that is wy i already "blocked" the line 'On Error Resume Next.
Sub is running till the end i guess otherwhise it won't clear the last named range in the code.

On the sheet"data" no cells are protected any way.