Using variable sheetname in VBA

Question

I'm a novice at VBA (just starting to learn). In my userform I have a combobox with values from 1 to 12(not string) representing the months. I want the user to pick a month and based on that, the multiple listboxes and labels I have placed should get filled by the relevant values in one of the 12 sheets representing each month. as I am a novice I have a lot of problems here but for starters the following lines do not seem to work on userform_initiate()

For j = 0 To 1
    arr_trh(0, j) = Sheets("Sheet6").Cells(4, j + 1)
Next j

I can get it to work for a single sheet by using

arr_trh(0, j) = Sheet6.Cells(4, j + 1)

However, what I'm trying to do later on is to create a string and somehow concatenate "Sheet" and combobox value to pass on to Sheets() function.

Any help would be appreciated Thanks

"the following lines do not seem to work" -- that isn't a very helpful problem statement. Are you getting an error message? If so -- what? On what line? — John Coleman
– John Coleman, Commented May 20, 2018 at 13:33
A userform and a worksheet are not the same thing.I think you're working with worksheets, so avoid any instruction that has to do with userforms for now. The worksheet is the spreadsheet, with tabs at the bottom; user forms are custom form elements designed in the Visual Basic Editor. A Google search will explain better. — ashleedawg
– ashleedawg, Commented May 20, 2018 at 13:33
What about Sheets("Sheet" & x).Cells()? Where x is string type variable and x gets value from combobox. — Harun24hr
– Harun24hr, Commented May 20, 2018 at 14:35

Gary's Student · Accepted Answer · 2018-05-20 13:44:14Z

1

Rather than referring to the Sheet object like:

v = Sheet1.Range("A1")

use:

v = Sheets(1).Range("A1")

which you can index like:

v = Sheets(i).Range("A1")

where i is a variable.

edited May 20, 2018 at 13:44

answered May 20, 2018 at 13:35

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AmMik Over a year ago

Excellent! that seems to have worked, I had no idea you could use it like that. Thanks John and Ashlee for the answers as well. Now to see if I can assign combobox selection to it.

JohnyL · Accepted Answer · 2018-05-20 14:22:33Z

0

I guess, my this answer will help you to figure out how to refer to sheets. Also, it tells about caveats of Index property.

answered May 20, 2018 at 14:22

JohnyL

7,1845 gold badges27 silver badges48 bronze badges

1 Comment

AmMik Over a year ago

That was an excellent post and it helped me figurd out what's going on a lot. Thanks!

Collectives™ on Stack Overflow

Using variable sheetname in VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related