VBA: Named Ranges on Different Sheets

[mykulpasskwa]

I'm making a mess of this code trying to get this to work. When a user opens the workbook they have an option to select a worksheet and whatever they select only shows their selected worksheet and hides the rest. I have 8 worksheets and 6 of them have one cell named "ShBal#" (ShBal1, ShBal2, ShBal3,etc.). Those same 6 sheets have another named range "BanBal#". The address is different on every worksheet, but if those ranges match each other (e.g. ShBal1 = BanBal1) then a msgbox appears saying they've matched and asks if you want to save.

I can't figure out to get VBA to find the named range on the sheet so the code that finds the match works. I've tried setting variables and doing For Loops but I'm not doing something right. Any advice? Please excuse what I have below, I've been trying this for a while and didn't clean up everything I have.

Sub Match()
'PURPOSE: if the sheet balance and Ban balance match then you are notified of match and asked to save.
Dim a As Range
Dim b As Currency
Dim answer As Integer
Dim ID As Variant
Dim Fname As String
Dim Target As Range
Dim ShBal As Range
Dim x As Integer

On Error Resume Next 'this should only be a temporary solution, need to fix variables
     
     For x = 1 To 6
        If Target.Address = ActiveSheet.Range("ShBal" & x).Address Then 
    Next
        
'a = Range("ShBal").Address
'b = Range("BanBal").Address


ID = ActiveSheet.Range("ShID*").Value

    If b = "" And a = 0 Then
    Exit Sub
        ElseIf ActiveSheet.Range("ShBal*").Value = ActiveSheet.Range("BanBal*").Value Then
        MsgBox ("The Account Overview Balance and Ban Balance Match." & vbNewLine & "Sheet balance is $ " & a & vbNewLine & "Banner balance is $ " & b)
        answer = MsgBox("Would you like to save?", vbYesNo + vbQuestion)
    If answer = vbYes Then
        Fname = Application.GetSaveAsFilename(ID & " - Account Overview")
        ThisWorkbook.SaveAs Fname & "xlsm", 52
    Else
        'do nothing
    End If
    End If
End Sub

 

[Jeffrey Mahoney]

I think what you need to do is Create your named ranges as SHEET level. You can have duplicate named ranges on many sheets if they are created as SHEET Level.

Then you don't have to differentiate between the different sheet names to determine the correct ShBal and BanBal.

 

[CephasOz]

My first thoughts were the same as Jeffrey's. However, if you prefer to continue along your current path, you might incorporate this code fragment:

    Dim strSheetNo As String
    Dim intSheetNo As Integer
    Dim rngShBal As Range
    Dim rngBanBal As Range
    ' Find the number to use.
    For intSheetNo = 1 To 6
        If (ThisWorkbook.Names("ShBal" & Format(intSheetNo)).RefersToRange.Parent.Name = ActiveSheet.Name) Then
            strSheetNo = Format(intSheetNo)
            Exit For
        End If
    Next
    Set rngShBal = ActiveSheet.Range("ShBal" & strSheetNo)
    Set rngBanBal = ActiveSheet.Range("BanBal" & strSheetNo)

 

[Jeffrey Mahoney]

What I like about Sheet level named ranges is that you can copy a sheet and not have to change the named ranges. Much easier to manage.

 

[mykulpasskwa]

What I like about Sheet level named ranges is that you can copy a sheet and not have to change the named ranges. Much easier to manage.

Thanks, Jeffrey and CephasOz. Jeffrey, I'm definitely going to change my names to the sheet level. I'm using named ranges more now, but haven't much in the past, so I didn't realize this was a capability. Finding out new tricks is one my favorite things about this forum. Thanks again!