Is there a way to ReName a Sheet Using a Drop Down Menu?

[Joe R.]

I've placed a drop down menu (which doesn't print when the
sheet is printed) on a sheet. The choice selected from
the menu is placed in a cell on the spreadsheet and this
cell is printed.

What I'd like to do is rename the sheet to the value
chosen from the drop down menu. Assuming this is
possible, how is this done?

 

[Heapy]

Assuming your combobox is added to the sheet and not to a userform, and
assuming it's called "ComboBox1", create a change event that changes the
sheet name. e.g.:

Private Sub ComboBox1_Change()

ActiveSheet.Name = ComboBox1.Value

End Sub


This changes the sheet name to whatever the value of the combobox is.
I think this is what you were looking for...

HWH

 

[Otto Moehrbach]

To add to what Heapy gave you. If your drop-down box is simply a Data
Validation cell in say, A1, then you can use:
ActiveSheet.Name = Range("A1").Value
You would place this line in a Worksheet_Change event macro in the sheet
module. You would need to add code to your macro to restrict this action to
a change in only that one cell (A1). Post back if this is not clear. HTH
Otto

 

[steve]

Otto,

Am using 97 and didn't think it worked. Thought I had tried it before. But
tried your code and IT WORKS!

This opens some interesting possibilities for me...

thanks...