Range name vs Sheet name [again Otto!]
 

I've got another idea and would appreciate any assistance.
The userform from which the user would select the stockitem, contains the
full product description in "D2" [absolute reference]
The sheet name might contain whatever name of the specific stockitem
Thus: The item the user selects from the list must contain the sheet name
[whatever that might be].
My question: What is the possibility that the sheet can be identified solely
on the users choice and activate the sheet?
I know you wise guys will be able to help me
Thanks
--
HJN

Range name vs Sheet name [again Otto!]
 

You can activate any sheet by its name

Sheet("My Name Sheet").Activate

However looking at the earlier posts you appear to rely on sheet names (that
the user can change) to select an item. As was said by others this is not a
good idea. I would never use the sheet name as an index system that my code
relied on. If you wanted to list the sheet names in a drop down list and
select from that, you could use something like this in the UserForm
code.....

Private Sub UserForm_Initialize()
Dim wS As Worksheet
With ComboBox1
.Clear
For Each wS In Worksheets
.AddItem wS.Name
Next
End With
End Sub

Then set the combobox change event to this code

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex -1 Then
Sheets(ComboBox1.Value).Activate
End If
End Sub

--

Regards,
Nigel




"Hennie Neuhoff" wrote in message
...
I've got another idea and would appreciate any assistance.
The userform from which the user would select the stockitem, contains the
full product description in "D2" [absolute reference]
The sheet name might contain whatever name of the specific stockitem
Thus: The item the user selects from the list must contain the sheet name
[whatever that might be].
My question: What is the possibility that the sheet can be identified
solely
on the users choice and activate the sheet?
I know you wise guys will be able to help me
Thanks
--
HJN

Range name vs Sheet name [again Otto!]
 

Why don't you put the stock item as a drop down box in cell D2. Then look
for the name in D2 and compare to your listbox to find the sheet

for each sht in sheets
if sht.Range("D2") = listbox1.value then
Set MatchSht = sht
exit for
end if
next sht

with Matchsht
;enter your cod ehere

end iwth


"Hennie Neuhoff" wrote:

I've got another idea and would appreciate any assistance.
The userform from which the user would select the stockitem, contains the
full product description in "D2" [absolute reference]
The sheet name might contain whatever name of the specific stockitem
Thus: The item the user selects from the list must contain the sheet name
[whatever that might be].
My question: What is the possibility that the sheet can be identified solely
on the users choice and activate the sheet?
I know you wise guys will be able to help me
Thanks
--
HJN