VBA: Named Ranges on Different Sheets

mykulpasskwa

Board Regular
Joined
Mar 20, 2018
Messages
65
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,823
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

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
VBA Code:
    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

Well-known Member
Joined
May 31, 2015
Messages
1,823
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

Board Regular
Joined
Mar 20, 2018
Messages
65
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,280
Messages
5,571,289
Members
412,376
Latest member
osborne
Top