How to dynamically change code when sheet name is changed?

user_9006778 0
Viewed 120

I'm trying to write a macro that combine the defined sheets from different workbooks and saves them to a separate workbook. Each workbook contains a sheet with the same name MODEL. This sheet has a combobox, this combobox is associated with the worksheet name like in code below:

Sub ComboBox1_Change()
Application.ScreenUpdating = False
Select Case Sheets("MODEL").Range("code_plant")
Case 1
Sheets("MODEL").Range("price_zero").Copy _
Destination:=Sheets("MODEL").Range("price_on_view")
End Select
Application.ScreenUpdating = True
End Sub

The problem arises when all the worksheets are combined together in one workbook and renamed. I need to fix my combobox macro that should after renaming refer to the current (renamed) sheet name. I was trying to use the following function but can't figure out how it may be include in my code in right way.

Function MySheet()
Application.Volatile
MySheet = Application.Caller.Worksheet.CodeName
End Function

Thanks!

vba excel
2 Answers

Your ComboBox1_Change event is declared in the worksheet module, so you should be using the Me keyword in your code, to refer to the sheet, instead of finding the sheet by name with Sheets("MODEL").

Change your event handlers, in each source sheet to look like this:

Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    Select Case Me.Range("code_plant")
        Case 1
            Me.Range("price_zero").Copy _
                Destination:=Me.Range("price_on_view")
    End Select
    Application.ScreenUpdating = True
End Sub
user_57571590

Since your combobox is in the same sheet, you do not need to specify the sheetname or use anything else to qualify it. The range will point to Activesheet (Where ComboBox1 resides) which is the Sheets("MODEL") or whatever it's new name is.

Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    Select Case Range("code_plant")
        Case 1: Range("price_zero").Copy Range("price_on_view")
    End Select
    Application.ScreenUpdating = True
End Sub
user_11405790
Related