Listbox not working when called from a command button

Dedeke

Board Regular
Joined
Dec 1, 2020
Messages
70
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: 3
  • Form on start.JPG
    Form on start.JPG
    57.3 KB · Views: 2

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You need to qualify the ranges in the click event like
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 = ws.Cells(sRow, 1) 'value from Column 5 (E)
    Me.TextBox8.Text = ws.Cells(sRow, 2)
    Me.TextBox9.Text = ws.Cells(sRow, 3)
    Me.TextBox10.Text = ws.Cells(sRow, 4)
End Sub
 
Upvote 0
You need to qualify the ranges in the click event like
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 = ws.Cells(sRow, 1) 'value from Column 5 (E)
    Me.TextBox8.Text = ws.Cells(sRow, 2)
    Me.TextBox9.Text = ws.Cells(sRow, 3)
    Me.TextBox10.Text = ws.Cells(sRow, 4)
End Sub
Hello Fluff,

Is it possible to give an example or to change my code correctly.
Tried some things myself but still not working.
Like i said before, al i recieved till now is with help from here ore trying some things out
 
Upvote 0
I've already done that in post#2 ;)
 
Upvote 0
Look at the word Cell & what's just in front of it.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You're welcome & thanks for the feedback.
Fluff
Same problem with this one
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 = Cells(Rows.Count, "E").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, "A")
        newNm = ws.Cells(r, "E")
'       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, "A") = newNm
                
                End If
            On Error GoTo 0

        End If

    Next r
    ws.Range("E2:E12" & Lrow).ClearContents
    Application.ScreenUpdating = True
End Sub

code for buttons
Code:
Private Sub CommandButton3_Click()
Call MyRenameSheets1
End Sub
 
Upvote 0
You need to qualify the Cells on this line as well
VBA Code:
Lrow = Cells(Rows.Count, "E").End(xlUp).Row
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top