[VBA] Trying to use sheet names in listbox as source to run code - can't figure out reference

[rossross]

Trying to keep this as simple and clear as possible as it is pretty involved but happy to post more to get to a solution if necessary.

I've got a command that works. I'm trying to shift from using a cell (cell is a named range) where you input a sheet name and it does the action on that sheet.

What I would like to do is press command button on dashboard/home page, draw up a list of sheets available and then do the command on the sheet that way.

Currently the process just says something like

 with named range, ...... do the command

i'd like for it to be something like

with source .... do the command

the source being my list of sheet names in the listbox/userform

What I've got so far that works...

Looping through to get sheet names:

Sub GetList()

Dim ws As Worksheet
Dim x As Integer

    x = 1

    Sheets("mpage").Range("O:O").Clear

    For Each ws In Worksheets
        Sheets("mpage").Cells(x, 15) = ws.Name
        x = x + 1
    Next ws

End Sub

gets me my list. prints to the designated range on mpage.


getting my named range i use as rowsource for the listbox:

Sub namelist()

Dim ws As Worksheet
Dim namerange As Range
Dim rangename As String
Dim lastrow As Long
Dim usedcolumn As Long
Dim startcell As Range

Set ws = ThisWorkbook.Sheets("mpage")
Set startcell = ws.Range("O2")
lastrow = ws.Cells(ws.Rows.Count, startcell.Column).End(xlUp).Row
usedcolumn = ws.Cells(startcell.Row, ws.Columns.Count).End(xlToRight).Column

Set namerange = ws.Range(startcell, ws.Cells(lastrow, usedcolumn))

rangename = "SheetList"

ThisWorkbook.Names.Add Name:=rangename, RefersTo:=namerange

End Sub

both of these work no issues. and my userform populates as it should.

this is where my command comes in, and i'll cut it short as the rest isn't relevant (it doesn't call on the worksheet anymore)

Sub cmdv2(shtname As String)

Dim ws As Worksheet
Dim home As Worksheet
Dim raw As Worksheet
Dim market As Worksheet
Dim ss As Worksheet
Dim i As Long
Dim mets(10, 2) As String



Set ws = ThisWorkbook.Sheets(shtname)
Set home = ThisWorkbook.Sheets("Home")
Set market = ThisWorkbook.Sheets("Market")
Set ss = ThisWorkbook.Sheets("ss")

With home
    For i = 0 To 12
        If Len(.Range("I" & (i + 3))) > 0 Then
        mets(i, 0) = .Range("I" & (i + 3))
        mets(i, 1) = ThisWorkbook.Sheets("Raw").Range(mets(i, 0) & "1")
        
            With market
                .Select
                .Range("A1").Select
                .Cells.Find(what:=mets(i, 1), after:=ActiveCell, LookIn:=xlFormulas, _
                    lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
                    MatchCase:=False, searchformat:=False).Activate
            End With
        
            mets(i, 2) = collett(ActiveCell.Column)
        
            With ws
                mets(i, 3) = .Range(mets(i, 2) & "2")
                mets(i, 4) = .Range(mets(i, 2) & "4")
                mets(i, 5) = .Range(mets(i, 2) & "5")

now, what it does here doesn't really matter, i don't think it's essential to what i'm trying to figure out. But again happy to explain if needed.

the bit where it says shtname as string and ws = shtname was recommended by someone else. i don't quite understand it. the code ends with "set ws = nothing"


from here i've got

Private Sub worksheet_beforedoubleclick(ByVal target As Range, Cancel As Boolean)

Dim shtname As String
shtname = target.Text

Call cmdv2(shtname)

Cancel = True


End Sub


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


Dim shtname As String
shtname = target.Text


Call cmdv2(shtname)

Cancel = True


End Sub

yes, they both say the same thing. double clicking with the first "Before double click" did nothing and now the second part tells me that i've got an object required error.


i feel like this is extremely simple but i just don't know how to do it.

To summarize: click button, userform shows, select sheet from pre-generated list, click button, this sheet you've selected is then summarized and posted to another sheet.


please help me out here.

 

[rossross]

worth noting i found an old thread on here where the code in the listbox is simply

Private Sub ListBox1_Click()

Worksheets(ListBox1.Value).Activate

End Sub

i then went into my module above and edited

set ws = listbox1.value

now i'm just getting an object not defined error here. so that's better i think? still don't know how to make it work but i think i'm a step closer