apply error handeling if user selects wrong file based on pre-defined sheets

[carissa7]

I have VBA code that prompts a user to select the workbook from a location. I want to add error handling so that if they choose an incorrect file based on the worksheets then apply a message box "wrong file" and re-enable the prompt to choose a new file.

As you can see below I've already set what the existing names of the worksheets should be if they select the correct workbook.


Sub Button4_Click()

Application.ScreenUpdating = False

Application.EnableEvents = False

Dim strFileName As String
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim ws1A As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim cell As Range
Dim rng As Range
Dim rng2 As Range
Dim RangeName As String
Dim CellName As String
Dim ValueToFind
Dim dstRng As Range
Dim NewFile As Variant


'prompt folder location
NewFile = Application.GetOpenFilename("microsoft excel files (*.xlsm*), *.xlsm*")

If NewFile <> False Then
Set wb1 = Workbooks.Open(NewFile)
'apply error handeling if user does not select a file
Else
MsgBox "No/Wrong File Selected. Program Will Exit."
Application.DisplayAlerts = False
Exit Sub
End If


Set wb2 = ThisWorkbook
Set ws2 = wb2.Sheets("Output")
Set ws1 = wb1.Sheets("RVP Local GAAP")
Set rng = Range("CurrentTaxPerLocalGAAPProvision")
Set rng2 = Range("CurrentTaxPerGroupGAAPProvision")
Set ws1A = wb1.Sheets("RVP Group GAAP")


'Loop through all the values in NamedRange
For Each rng In ws2.Range("NamedRange")
Set dstRng = Nothing
On Error Resume Next
Set dstRng = ws1.Range(rng.Value)
On Error GoTo 0
'Check that the range exists in destination sheet
If Not dstRng Is Nothing Then
'Check that the range exists in the appropriate area
If Not Intersect(dstRng, ws1.Range("CurrentTaxPerLocalGAAPProvision")) Is Nothing Then
'Transfer the value from the next column to the appropriate range in the
'destination sheet
dstRng.Value = rng.Offset(0, 1).Value
Else
End If
End If
Next
For Each rng2 In ws2.Range("NamedRange")
Set dstRng = Nothing
On Error Resume Next
Set dstRng = ws1A.Range(rng2.Value)
On Error GoTo 0
'Check that the range exists in destination sheet
If Not dstRng Is Nothing Then
'Check that the range exists in the appropriate area
If Not Intersect(dstRng, ws1A.Range("CurrentTaxPerGroupGAAPProvision")) Is Nothing Then
''MsgBox "succesful"
''found = False
'Transfer the value from the next column to the appropriate range in the
'destination sheet
dstRng.Value = rng2.Offset(0, 1).Value
Else
End If
End If
Next
MsgBox "Values have copied over sucessfully"

ActiveWindow.FreezePanes = False
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

[AlphaFrog]

Please Login or Register  to view this content.

[carissa7]

thanks much.. worked perfectly...can I ask another question related to error handeling in the same code above...
I'm having trouble with this line of code --> ElseIf rng2.Value <> dstRng Then MsgBox rng2.Value & " not in RVP Group GAAP sheet" is not being recognized and I made sure to set values not equal to each other. Basically I am trying to apply error handling to my if statement below. It works perfectly when the two ranges match just not when values do not match...

Please Login or Register  to view this content.

[AlphaFrog]

You're welcome.

Not sure I follow what's going on. It looks like rng2.Value is the "Name" for a range that is set to the variable dstRng. If that's correct, then this; ElseIf rng2.Value <> dstRng Then, is comparing the Range Name to the Range Value. I think you just want an Else statement (not ElseIf) as you've previously tested if the dstRng is in the RVP group. You may want that that Else statement as part of the first IF; If Not dstRng Is Nothing Then.

[carissa7]

Got it okay so I added the else statement --> Else: MsgBox rng.Value & " not in RVP Local GAAP sheet"
and it did work and returned a message box for the value that was missing in the destination sheet, however it didn't copy over the successful values? and then it gives me an error message "Invalid procedure call or argument" on this line --> If Not Intersect(dstRng, checkRange) Is Nothing Then

Please Login or Register  to view this content.

Next

[AlphaFrog]

Please Login or Register  to view this content.

[carissa7]

For some reason the above just repetitively shows both message boxes for all values even the successful ones and then I have to force quit to stop the message boxes... heres my code:

Please Login or Register  to view this content.

[AlphaFrog]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[carissa7]

great thanks so much - the template file is the one to choose when you get the prompt while the code is in workbook 2.

[AlphaFrog]

Your named range ws2.Range("NamedRange") is defined as all cells in column D. So your code was looping through the whole column including all the empty cells. That's why there was a continuous loop of no matches.

The code below defines the named range from D4 to the last used cell in column D.

There are only four named ranges on each sheet in the template. So you will still have several messages that there is no named range match. I don't know if that's what you want or not.

Please Login or Register  to view this content.

[carissa7]

thanks for pointing this out, it does help. That is fine that there is only 4 named ranges on each sheet with a match... however the message box still appears for even those named ranges that do have a match and it titles it "no named range." Also right now it doesn't even copy over the successful values, it just shows me the message box.

[AlphaFrog]

Using your example workbooks, it matched one range on each sheet and copied that value over. If you think there should be more matches, check that the values in column D are the exact names of the Named ranges in template workbook (case sensitive)

I don't understand what you want the message box to do or not do.

[carissa7]

I only want the message box to show when there is no match between the namedrange column and the destination sheet. if there is a no match then just let the user know that there is no match via a message box. For some reason the no match is showing for every single named range i have in the namedrange column.

[AlphaFrog]

I only want the message box to show when there is no match between the namedrange column and the destination sheet. if there is a no match then just let the user know that there is no match via a message box.

Please Login or Register  to view this content.

[carissa7]

ah yes looks like that works.. thanks for all your help and effort on this!!! It is much appreciated

[carissa7]

per your suggestion writing this in the main thread- using the same workbooks

Basically right now it prompts the user to select the template and performs the code perfectly, instead I want it to prompt the user to choose a folder the folder will be called “templates” and it will contain all the templates that I want it to perform the code on and then save them into an output folder called “output”

The code works by looking to see if column (“NamedRange”) exists as a named range in the template and then returns the report balance if it does exist.
However I want to add logic where for each template in the folder it will check the “Ted ID” in a table in ws "output - flat" and only bring back the values associated with that Ted ID.
So in the table there are two different Ted ID’s so there will be two templates outputs

The table is in “output - flat” tab in “workboopk2” along with a “select all” checkbox. I want the code to only loop through all the workbooks and perform the above when the user clicks “ select all” and then “Send values” button.

Let me know if this makes sense and thanks so much...

[carissa7]

The code is in Module 1 of “workbook2” and the template to choose from the folder is attached. both are attached.

Basically right now it prompts the user to select the template and performs the code perfectly, instead I want it to prompt the user to choose a folder the folder will be called “templates” and it will contain all the templates then I want it to perform the code on and then save them into an output folder called “output”

The code works by looking to see if column (“NamedRange”) exists as a named range in the template and then returns the report balance if it does exist.
However I want to add logic where for each template in the folder it will check the “Ted ID” in a table in ws "output - flat" and only bring back the values associated with that Ted ID.
So in the table there are two different Ted ID’s so there will be two templates outputs

The table is in “output - flat” tab in “workboopk2” along with a “select all” checkbox. I want the code to only loop through all the workbooks and perform the above when the user clicks “ select all” and then “Send values” button.