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...

Set wsh = WorkSheets(ComboBox.Value)

Et voilà!

HTH
--
AP


"Kozak" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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...
>

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.

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.

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.