Excel VBA for Changing Tab Name & Return Blank Cell to Formula

[DjTRex2002]

Hello, ok so far I am barely learning how to write code by doing a lot of searching on google lol. I wanted to see if you could help me with the following ... I am trying to change the Sheet Tab by getting information from Cell B, which works great and the code is ...

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230130
    On Error Resume Next   
        If Not Intersect(Target, Range("B3")) Is Nothing Then   
            ActiveSheet.Name = ActiveSheet.Range("B3")       
        ElseIf Not Intersect(Target.Dependents, Range("B3")) Then   
    ActiveSheet.Name = ActiveSheet.Range("B3")     
End If
End Sub

Also have the following to return a blank cell to formula ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C9").Value = "" Then
        Range("C9").Formula = "=A9*B9"
End If
    If Range("C10").Value = "" Then
        Range("C10").Formula = "=A10*B10"
End If
End Sub

However, I do not know how to combine the two codes. It gives me an error ...

Also, can the formula to return on a blank cell be "=IF(F17<>"",VLOOKUP(F17,Parts!$A:$C,2,FALSE),"")" instead of "=A10*B10"?

Thank you

 

[Micron]

You could google the error message and learn what causes it? Simply, you cannot have 2 procedures on the same module (sheet, userform or standard module) with the same name. In your case, copying/pasting the second procedure's lines (not including Sub, End Sub lines) into the first should work but you will have to delete the second one before you can run either of them. I think I would copy/paste first, then comment out the entire second procedure and see what you get.

 

[DjTRex2002]

You could google the error message and learn what causes it? Simply, you cannot have 2 procedures on the same module (sheet, userform or standard module) with the same name. In your case, copying/pasting the second procedure's lines (not including Sub, End Sub lines) into the first should work but you will have to delete the second one before you can run either of them. I think I would copy/paste first, then comment out the entire second procedure and see what you get.

I been searching Google for days and cannot find an answer. All I learned is that you cannot have "Same Worksheet" in the code. I tried combining them and that's when I get the error. Hence, this is why I posted help on this.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230130
    On Error Resume Next
        If Not Intersect(Target, Range("B3")) Is Nothing Then
            ActiveSheet.Name = ActiveSheet.Range("B3")
        ElseIf Not Intersect(Target.Dependents, Range("B3")) Then
    ActiveSheet.Name = ActiveSheet.Range("B3")
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("G17").Value = "" Then
        Range("G17").Formula = "=IF(F17<>"",VLOOKUP(F17,Parts!$A:$C,2,FALSE),"")"
End If
    If Range("G18").Value = "" Then
        Range("G18").Formula = "=IF(F18<>"",VLOOKUP(F18,Parts!$A:$C,2,FALSE),"")"
End If
End Sub

That's when I get the "Compile Error: Ambiguous name detected: Worksheet_Change" ... I learned that something needs to change in the line that says "Private Sub Worksheet_Change..." but have no clue as to what.

 

[Micron]

I explained the reason but if you still have no clue then I guess I didn't explain it well enough. Some people refer to procedures in Excel as macros but coming from Access mainly, I usually don't.

If you have code for sheet1 (we sometimes say "on" or "in" or "behind" the sheet) you can have many procedures. No two of them can have the same name or you will get that error. You are trying to do 2 different things using 2 subs/procedures with the same name. These must be on the same sheet, otherwise you wouldn't get the error.

Sheet2 can have procedures with the same name(s) as Sheet1 because they are on different sheets (the code behind the sheet is called a module). We say that code on sheet1 is out of scope to sheet2; i.e. one is not "visible" to the other.
There are also "containers" (modules) that don't belong to any sheet. They serve a different purpose - usually to allow the same procedures to be used by many sheets or userforms. These are known as standard modules. You may not have any in your workbook but you can insert one for fun if you wish. Any standard module cannot have 2 procedures in the same one with the same name(s) either.

Last but not least, userforms have their own modules. Userform modules are like sheet modules. One form cannot have 2 procedures with the same name either.

You can try this to see if it does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230130
On Error Resume Next
If Not Intersect(Target, Range("B3")) Is Nothing Then
    ActiveSheet.Name = ActiveSheet.Range("B3")
ElseIf Not Intersect(Target.Dependents, Range("B3")) Then
    ActiveSheet.Name = ActiveSheet.Range("B3")
End If

If Range("G17").Value = "" Then
    Range("G17").Formula = "=IF(F17<>"",VLOOKUP(F17,Parts!$A:$C,2,FALSE),"")"
End If
If Range("G18").Value = "" Then
    Range("G18").Formula = "=IF(F18<>"",VLOOKUP(F18,Parts!$A:$C,2,FALSE),"")"
End If

End Sub

 

[DjTRex2002]

Thanks for Micron, however, it just leaves the cell blank and doesn't put the formula.

 

[DjTRex2002]

I created a recorded macros that works, but I don't want that handle the workbook to be hitting Ctrl+Shift+G or +M every time ...

Sub Macro1()
'
' Macro1 Macro
' Return Formula
'
' Keyboard Shortcut: Ctrl+Shift+G
'
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<>"""",VLOOKUP(RC[-1],Parts!C1:C3,2,FALSE),"""")"
    Range("M17").Select
End Sub
Sub Macro2()
'
' Macro2 Macro
' Return Formula
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-8]<>"""",VLOOKUP(RC[-7],Parts!C1:C3,3,FALSE),"""")"
    Range("M18").Select
End Sub

Not so sure how this macros works because the range cells that replaces the code is on cells G17:L17 (Merged cells) with "=IF(F17<>"",VLOOKUP(F17,Parts!$A:$C,2,FALSE),"")" and what I have on cell M17 is "=IF(E17<>"",VLOOKUP(F17,Parts!$A:$C,3,FALSE),"")" ... but somehow it works lol