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