Multiple VbQuestions Fn intersected with loop IF

[mostafanabilemostafa]

I need to use vbquestion 4 times as below
1. The first one asks if you need to upload the first file or not
2. Then asks for the second file
3. Then asks for the third file
4. Then asks for the fourth file
5. Lastly shows a message for the selected files using a string variabe if the file was the correct one it its value is set to Selected if not it is selected to Skipped, then these global variables are called lastly in the last message

i used this code and it works fine



Dim UploadedP As String
Dim UploadedR As String
Dim UploadedM As String
Dim UploadedV As String

Sub Assistant_Mode_Raw()

If MsgBox("Do You Have a raw file to be uploaded?", _
vbQuestion + vbYesNo, "Checking Raw File Existance") = vbYes Then
Z_Import_Raw_SIMs_2 'Calling another module code by its name
UploadedR = "Selected"
Assistant_Mode_Package
Else
UploadedR = "Skipped"
Assistant_Mode_Package
End If
End Sub


Sub Assistant_Mode_Package()

If MsgBox("Do You Have a Package Query file to be uploaded?", _
vbQuestion + vbYesNo, "Checking Package Query File Existance") = vbYes Then
Z_Import_Package_SIMs_2 'Calling another module code by its name
UploadedP = "Selected"
Assistant_Mode_Mobile
Else
UploadedP = "Skipped"
Assistant_Mode_Mobile
End If
End Sub


Sub Assistant_Mode_Mobile()

If MsgBox("Do You Have a Mobile Terminals Query file to be uploaded?", _
vbQuestion + vbYesNo, "Checking Mobile Terminals Query File Existance") = vbYes Then
Z_Import_Mobile_2 'Calling another module code by its name
UploadedM = "Selected"
Assistant_Mode_Vouchers
Else
UploadedM = "Skipped"
Assistant_Mode_Vouchers
End If
End Sub

Sub Assistant_Mode_Vouchers()

If MsgBox("Do You Have a Vocuher Query file to be uploaded?", _
vbQuestion + vbYesNo, "Checking Vocuher Query File Existance") = vbYes Then
Z_Import_Voucher_2 'Calling another module code by its name
UploadedV = "Selected"
Final_step
Else
UploadedV = "Skipped"
Final_step
End If
End Sub

Sub Final_step()

MsgBox "You have selected below BSS Files!" & vbNewLine & vbNewLine & " 1. SIM Card Query File : " & UploadedR & vbNewLine & " 2. Package Query File : " & UploadedP & vbNewLine & " 3. Mobile Terminals Query File : " & UploadedM & vbNewLine & " 4. Vouchers Query File : " & UploadedV & vbNewLine & vbNewLine & "Now Go To Generation Step!", Title:="Assistant Mode Process Notification!"

End Sub


up to this point everything goes ok. but i added some validation process in which let the uploaded file to be validated if it is correct one or not.

This validation process exists in the external called sub For improting the file for Ex (Z_Import_Raw_SIMs_2).

and so on for every file of the 4 file types of the 4 questions.

So to discuss the issue and make it more clear

lets assume that we run the code and will go through the 4 questions

If we clicked yes that we have the file then import function is called here the rule of validations comes

IF the file was incorrectly choosen another message asks if you still have the file if we clicked yes and chose the right file this time

the 2nd questions comes if we clicked no and the same for the 2 remaining questions

the Expected result is to end processing after the final pop up message of Final_step () that telling us the selected files and the skipped ones

but the problem comes here then due to the first incorrectly chossen file.

as instead of end processing
it asks again for the non selected if we still need to import them or not

While trying to debug i found that
After perfoming the last command of Final_step () , it comes to the 3rd question to pass by end if then pass by its end sub and do the same for the 2nd question and the first one
then suddenly the debug curser moves to
EndIF
of the validation part

'First Validation for uploaded file using File Name
If InStr(OpenBookR2.Name, "Voucher") > 0 Or InStr(OpenBookR2.Name, "Mobile") Or InStr(OpenBookR2.Name, "Package") Then
SaveChanges = False
OpenBookR2.Close
MsgBox "You have not Chosen the right BSS File!" & vbNewLine & vbNewLine & "Please choose the right BSS RAW SIM File", Title:="Wrong Processing Notification!"
Assistant_Mode_Raw
End If


which is part of the import function like below

FileToOpenR2 = Application.GetOpenFilename(Title:="Browse for Raw SIMs File & Upload it", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpenR2 <> False Then
Set OpenBookR2 = Application.Workbooks.Open(FileToOpenR2)
Set Rsheet2 = OpenBookR2.Worksheets("Default")

'First Validation for uploaded file using File Name
If InStr(OpenBookR2.Name, "Voucher") > 0 Or InStr(OpenBookR2.Name, "Mobile") Or InStr(OpenBookR2.Name, "Package") Then
SaveChanges = False
OpenBookR2.Close
MsgBox "You have not Chosen the right BSS File!" & vbNewLine & vbNewLine & "Please choose the right BSS RAW SIM File", Title:="Wrong Processing Notification!"
Assistant_Mode_Raw
End If


so any recommdantion to fix this looped IF

 

[mart37]

When calling the same macro again you create an unexpected loop.
You can make a loop in the calling macro and a boolean to check the error in the import macro.

Dim Check as Boolean

Sub Assistant_Mode_Raw()
Check=True
do
  Q = MsgBox("Do You Have a raw file to be uploaded?",  vbQuestion + vbYesNo, "Checking Raw File Existance")
  If Q = vbYes Then Z_Import_Raw_SIMs_2 'Calling another module code by its name
loop until Q=vbNo or Check=True
if Q = vbYes then
  UploadedR = "Selected"
  Assistant_Mode_Package
Else
  UploadedR = "Skipped"
  Assistant_Mode_Package
End If
End Sub

'First Validation for uploaded file using File Name
If InStr(OpenBookR2.Name, "Voucher") > 0 Or InStr(OpenBookR2.Name, "Mobile") Or InStr(OpenBookR2.Name, "Package") Then
  SaveChanges = False
  OpenBookR2.Close
  MsgBox "You have not Chosen the right BSS File!" & vbNewLine & vbNewLine & "Please choose the right BSS RAW SIM File", Title:="Wrong Processing Notification!"
  Check = False
End If

 

[mostafanabilemostafa]

Dear @mart37 first Thank you for your fast response but please note when i tried to run your recommandations it worked fine the same way the first code does but it did not solve the main issue which i complaint so i do not know how wrongly i performed your recommandations

Now our problem is at the other import and validate sub in which when i changed Assistant_Mode_Package with Check = False as Boolean too
When the file is checked and it is not incorrect one it should come back again to reask about this file which is not happing and passing to ask about the next question, so any help !!

Below code is modified too

FileToOpenR2 = Application.GetOpenFilename(Title:="Browse for Raw SIMs File & Upload it", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpenR2 <> False Then
        Set OpenBookR2 = Application.Workbooks.Open(FileToOpenR2)
        Set Rsheet2 = OpenBookR2.Worksheets("Default")
                          
'First Validation for uploaded file using File Name
  If InStr(OpenBookR2.Name, "Voucher") > 0 Or InStr(OpenBookR2.Name, "Mobile") Or InStr(OpenBookR2.Name, "Package") Then
         SaveChanges = False
         OpenBookR2.Close
  MsgBox "You have not Chosen the right BSS File!" & vbNewLine & vbNewLine & "Please choose the right BSS RAW SIM File", Title:="Wrong Processing Notification!"
  Check = False
  End If

As when the selected file goes to validation process the message is just end

 

[mart37]

Do you have changed Sub Assistant_Mode_Package() like Sub Assistant_Mode_Raw() ?

 

[mostafanabilemostafa]

@mart37 Yes i changed Assistant_Mode_Package & Assistant_Mode_Mobile & Assistant_Mode_Vouchers
Like Sub Assistant_Mode_Raw()

But the problem is that there is no reference of below validation to go back to the mentioned sub so how can we solve it without loop

'First Validation for uploaded file using File Name
  If InStr(OpenBookR2.Name, "Voucher") > 0 Or InStr(OpenBookR2.Name, "Mobile") Or InStr(OpenBookR2.Name, "Package") Then
         SaveChanges = False
         OpenBookR2.Close
  MsgBox "You have not Chosen the right BSS File!" & vbNewLine & vbNewLine & "Please choose the right BSS RAW SIM File", Title:="Wrong Processing Notification!"
  Check = False
  End If

 

[mart37]

When you call in a macro an other macro then when this second macro is finished it returns automatically to its owner (the first macro).

Sub FirstMacro()
Call SecondMacro
other code1
End Sub

Sub SecondMacro()
other code2
End sub

When the SecondMacro is finished the FirstMacro continues with OTHER CODE1

 

[mostafanabilemostafa]

so following this approach will lead us to the loop too
as when the file is validated and reach line of Assistant_Mode_Raw
the sub of Assistant_Mode_Raw is performed and returned to original sub which must pass by the last end if to close the if condition and returned again and so on which leads to unexpected loops

i wish i could figure out a better alternative for this but it seems i will not

If InStr(OpenBookR2.Name, "Voucher") > 0 Or InStr(OpenBookR2.Name, "Mobile") Or InStr(OpenBookR2.Name, "Package") Then
         SaveChanges = False
         OpenBookR2.Close
  MsgBox "You have not Chosen the right BSS File!" & vbNewLine & vbNewLine & "Please choose the right BSS RAW SIM File", Title:="Wrong Processing Notification!"
  Check = False
Assistant_Mode_Raw
  End If

 

[mart37]

Your last code leads to a loop.
You must delete Assistant_Mode_Raw

 

[mostafanabilemostafa]

Thank you once again, but for the last try before giving up on this alternative
this approach can not be done with any other means or codes or it will impossibe due to Vb limitations