0

(How to use Sheet name instead of Tab name in Excel VBA)?

How do I replace the tab's name ('Electrical') with the actual Sheet name (Sheet 11)? Whenever I do it doesn't work, here is the working code below, just need to use the sheet name:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target 

If .Address(False, False) = "D107" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=Electrical!I181"
Application.EnableEvents = True 
End If 
End If

This code is placed in the active sheet and it simply inputs a cell reference from another tab 'Electrical' tab (cell I181) unless the target cell (D107) already has a value (is not blank). And it should input it again if I clear (delete) the value in the target cell (D107).

Any help would be great - Thanks much!

3
  • When you say "it doesn't work" but also "here is the working code" it is a bit confusing. Please be as specific as possible. What do you mean it doesn't work? What error do you get? When does it work? Mar 22, 2022 at 1:18
  • Ps. There is no tab name, only a sheet name and a sheet index number (vba collection of sheets). Whatever name you give a sheet in Excel, that is its name in both Excel and VBA to use to access it. Your issue may rather be that your worksheet change event code also executes in other worksheets or even other workbooks. It is not local to only when that worksheet changes but rather the change and target range can be from any other open workbook/sheet. I think! Mar 22, 2022 at 1:28
  • OK, so what I mean by 'tab name' is that a user can change the name of the sheet by changing the name on the tab ('rename' tab). If I refer to that tab or sheet name (Electrical) then all works fine with the code I provided. However if I try to use the sheetcode name (Sheet 11) the code no longer works. It turns out the reason is because if you use .Formula then you must use the sheet name.
    – BobbyD
    Mar 23, 2022 at 7:12

1 Answer 1

0

A formula has to reference the tab name, but you can get the tab name from the code name.

.Formula = "=" & Sheet11.Name & "!I181"  

When entered to a sheet it will still show as =Electrical!I181 if that's what Sheet11 is called when the code runs.

If the formula is being entered on Sheet11 you can just use:

.Formula = "=I181"  

You could also use:

.FormulaR1C1 = "=" & Sheet11.Name & "!R181C9"

This points to row 181, column 9.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .