Excel 2010 VBA: Changing WorkSheet Names and Ribbons

[TechTank]

Hi All,

I'm using the following code to open specific ribbons based on the Worksheet name (I've setup my RibbonUI's in the XML already).

The code below opens up a particular ribbon based on the Worksheet Name or "CodeName" i.e. Sheet2:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Select Case Sh.CodeName
    Case "Sheet2": Call RefreshRibbon(Tag:="Environment")
    Case "Sheet3": Call RefreshRibbon(Tag:="Release")
    Case Else: Call RefreshRibbon(Tag:="")
    End Select
End Sub

Or I could use the WorkSheet Name i.e. Environment as below with the same code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     Select Case Sh.Name
     Case "Environment": Call RefreshRibbon(Tag:="Environment")
     Case "Release Plan Ver Draft 0.1": Call RefreshRibbon(Tag:="Release")
     Case Else: Call RefreshRibbon(Tag:="")
     End Select
 End Sub

This code looks to the Sheet Name\Sheet Code Name and then calls the relevent RibbonUI that I've created. The thing that's giving me trouble is that I can't seem to use a wildcard for something similar to this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      Select Case Sh.Name
    Case "Environment": Call RefreshRibbon(Tag:="Environment")
    Case "[B]Release Plan Ver *[/B]": Call RefreshRibbon(Tag:="Release")

I need this type of functionality as Users will make copies of this Worksheet and rename it to Release Plan Ver Draft 0.2\0.3\0.4 etc. The reason for changing the ribbon is that several macros on the Release Ribbon will error if they are used on the wrong Worksheet and possible corrupt data.

If anyone has any ideas how I could get this to work I would be very appreciative.

Many thanks,

Mark.

 

[RoryA]

Use an If...Else structure with Like:

If LCase(Sh.Name) = "environment" then
          Call RefreshRibbon(Tag:="Environment")
    ElseIf LCase(Sh.Name) Like "release plan ver *" Then
        Call RefreshRibbon(Tag:="Release")
End If

 

[TechTank]

Hi rorya,

Thank you for the fantastic bit of code there ! Works like a charm. Would it be possible to help me a little further? I tried to add an extra 'If' into your code but seem to have come unstuck. Ideally the entire code would look something like this (my problem area is in bold):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If LCase(Sh.Name) = "environment" Then
          Call RefreshRibbon(Tag:="Environment")
If LCase(Sh.Name) Like "release plan ver *" Then
        Call RefreshRibbon(Tag:="Release")
[B]ElseIf LCase(Sh.Name) Like "cover" Then
        Call RefreshRibbon(Tag:="")[/B]
  End If
End Sub

Much appreciated if you can help and sorry for being given an inch and stealing a mile :0)

Mark.

 

[RoryA]

After the first If, the others should all be ElseIf. You also don't appear to need Like in the new condition:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If LCase(Sh.Name) = "environment" Then
          Call RefreshRibbon(Tag:="Environment")
ElseIf LCase(Sh.Name) Like "release plan ver *" Then
        Call RefreshRibbon(Tag:="Release")
ElseIf LCase(Sh.Name) = "cover" Then
        Call RefreshRibbon(Tag:="")
  End If
End Sub

 

[TechTank]

Absolutely spot on!!! Thanks you so much for coming to the rescue.

Cheers rorya,

Mark.

 

[RoryA]

Glad to help.