Results 1 to 9 of 9
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    Please don't use ALL CAPS. It is the internet equivalent of shouting.

    The problem with your code it that it will work only once - once the sheet has another name then "CHEMICAL 1", you'll get an error message.
    Each worksheet also has a code name that does not change with the sheet tab. You can see this code name in the Visual Basic Editor in the (Name) property.
    Say that the (Name) of your sheet is Sheet7. You can then use

    Sheet7.Name = Me.CHEM1.Value

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    hello,

    I am wondering what i need to change in my syntax to have my combo boxes change the sheet name with the current value in the combo box, it seems because the sheet name can keep changing the name will have to be a varible



    Private Sub CHEM1_Change()
    Sheets("CHEMICAL 1").Name = Me.CHEM1.Value
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    thanks hans, sorry about the caps, i didnt want to sound like I was yelling.

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    If the user pick the same name as another sheet i be nice to have a msgbox pop up on a error event,

    Private Sub CHEM1_Change()
    ON ERROR goto msgBOX = "DUPLICATE SHEET NAME PLEASE USE UNIQUE NAMES!"
    Sheet2.Name = Left(Me.CHEM1.Value, 7)
    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    Private Sub CHEM1_Change()
    On Error GoTo ErrHandler
    Sheet2.Name = Left(Me.CHEM1.Value, 7)
    Exit Sub
    ErrHandler:
    MsgBox "Duplicate sheet name. Please use unique names!"
    End Sub

    (I don't like all caps in messages either) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    ok i will be nice in the messages <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    I want to make a button that can reset all the sheet names to default names and I think i am close with my syntax but seem to have a little problem getting the sheet object to communicate
    any suggestions?

    Sub resetsheetname_Click()
    Dim shtnum As Integer
    Dim i As Integer
    'Dim sheet As Object


    i = 2
    x = 1

    For i = 2 To 13
    Sheets("sheet" & i).Name = "CHEMICAL" & x
    x = x + 1
    i = i + 1
    Next

    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    By using Sheets("Sheet" & i) you are referring to the displayed name - but you have changed those. Try Sheets(i) instead.

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    thank you.

    Hans,

    for the error handler message is there a way to turn it of if i run another sub which is connect to this sub?

    Private Sub CHEM10_Change()
    On Error GoTo ErrHandler
    Sheet11.Name = Left(Me.chem10.Value, 7)
    Me.chembutton10.Caption = Left(Me.chem10.Value, 7)
    Exit Sub
    ErrHandler:
    MsgBox "Duplicate sheet name. Please use unique names!"
    End Sub

    This sub clears the the combo boxes and renames the sheets, but seem to run the error messaage in the above code.


    Sub resetsheetname_Click()
    Dim shtnum As Integer
    Dim i As Integer
    'Dim sheet As Object


    i = 2
    x = 1

    Do While i < 14
    Sheets(i).Name = "CHEMICAL" & x
    x = x + 1
    i = i + 1
    Loop
    On Error Resume Next
    Sheets("INPUT").Range("C12:c23").Clear

    End Sub

    Would a simple
    Application.DisplayAlerts = False

    work to turn off the msgs?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DROP DOWNS THAT NAME SHEETS (EXCEL MS OFFICE 2000)

    Add the following line add the beginning of CHEM10_Change:

    If CHEM10 = "" Then Exit Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •