Using a variable to gain access to worksheets through codenames.
 

Ok, I'm trying to figure out this issue. I have a combo box with
several values that a user has to choose from. These values for example
go from A - C. I have named three sheets Sheet1, Sheet2, and Sheet3.
They're codenames are A, B, and C respectively. How can I get access to
these sheets using the entry from the combo box?

ElseIf ComboBox.Value < "" Then
Dim RowUse As Long
Dim wsh As Worksheet
Set wsh = ComboBox.Value
RowUse = wsh.Range("A65536").End(xlUp).Offset(1, 0).Row
wsh.Cells(RowUse, "A").Value = NameBox.Text
wsh.Cells(RowUse, "B").Value = IDBox.Text

this doesnt work.. does anyone have any other ideas? So let me just
confirm .. using Sheets() and Worksheets(), what goes in the
parenthesis is the actual Name from the name property not the name from
(name) [the code name] correct?. I know u would be able to do
A.cells(RowUse,"A")... etc. but if i have 20 different sheets i dont
have to have an elseif for each sheet...

Using a variable to gain access to worksheets through codenames.
 

Set wsh = WorkSheets(ComboBox.Value)

Et voilà!

HTH
--
AP


"Kozak" a écrit dans le message de news:
...
Ok, I'm trying to figure out this issue. I have a combo box with
several values that a user has to choose from. These values for example
go from A - C. I have named three sheets Sheet1, Sheet2, and Sheet3.
They're codenames are A, B, and C respectively. How can I get access to
these sheets using the entry from the combo box?

ElseIf ComboBox.Value < "" Then
Dim RowUse As Long
Dim wsh As Worksheet
Set wsh = ComboBox.Value
RowUse = wsh.Range("A65536").End(xlUp).Offset(1, 0).Row
wsh.Cells(RowUse, "A").Value = NameBox.Text
wsh.Cells(RowUse, "B").Value = IDBox.Text

this doesnt work.. does anyone have any other ideas? So let me just
confirm .. using Sheets() and Worksheets(), what goes in the
parenthesis is the actual Name from the name property not the name from
(name) [the code name] correct?. I know u would be able to do
A.cells(RowUse,"A")... etc. but if i have 20 different sheets i dont
have to have an elseif for each sheet...

Using a variable to gain access to worksheets through codenames.
 

i get a subscript out of range error #9....
the sheets tab name are Sheet 1, Sheet 2, etc. but i wanna use the Code
name because it is A, B, C, etc. I dont want to change the sheet tab
name. when u do
worksheets(combobox.value) , that things that the combobox.value is the
tab name but its not. its the code name.

Using a variable to gain access to worksheets through codenames.
 

i get a subscript out of range error #9....
the sheets tab name are Sheet 1, Sheet 2, etc. but i wanna use the Code
name because it is A, B, C, etc. I dont want to change the sheet tab
name. when u do
worksheets(combobox.value) , that things that the combobox.value is the
tab name but its not. its the code name.

Using a variable to gain access to worksheets through codenames.
 

i get a subscript out of range error #9....
the sheets tab name are Sheet 1, Sheet 2, etc. but i wanna use the Code
name because it is A, B, C, etc. I dont want to change the sheet tab
name. when u do
worksheets(combobox.value) , that things that the combobox.value is the
tab name but its not. its the code name.