User Prompt Problem

[jroo]

Hello - I'm having issues with a user prompt message box not appearing when I call it from a userform that I created. Half the time it appears, and half the time it doesn't appear.

The user prompt is shown below in the second macro... "EditAlert". If i run the macro by itself (not calling it from another procedure), it works fine.

Initially I thought the problem could have resulted from me using the "Application.DisplayAlerts" messsages, but I don't think it's the issue. I'm using this code so that the "would you like to overwrite the existing file" message doesn't appear. I suppose I could get rid of the DisplayAlerts messages if there's an option in the workbook.SaveAs method for it, but I couldn't find it....

But back to my problem with calling the EditsPrompt from another macro.. Can anyone point out what I could be doing wrong (or inefficiently). Again, the user prompt message does show up half the time, but it's not consistent.

'*********Userform "Save Form" ***********

Sub CommandButton2_Click()


Me.Hide

Range("savepath").Formula = SaveForm.Savelistbox.Text

Set SaveWB = Workbooks.Add

ThisWorkbook.Sheets("DB").Range("exceldb").Copy
SaveWB.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=savepath
ActiveWindow.Close
Application.DisplayAlerts = True

If Me.OracleCheckbox = True Then
editprompt
End If

End Sub

'******** Edit Prompt ***********
Sub editprompt()
Application.DisplayAlerts = True
Dim EditAlert, Style, Title, Help, Ctxt, Answer

If IsError(Range("EditsCount").Value) Then
MsgBox "Inputs may be required. See oracle edits, lines 1503 to 1508"
gotoOracleEdits
Exit Sub
End If


If Range("EditsCount").Value > 0 Then
EditAlert = "There are Edits. Would you like to review the Edit Report before saving to Oracle?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Edit Alert"

Answer = MsgBox(EditAlert, Style, Title, Help, Ctxt)

If Answer = vbYes Then
gotoCSEDITS
Exit Sub
End If

If Answer = vbNo Then
oraclesave
'UpdateOracleSaveQuery
End If


End If

If Range("EditsCount").Value = 0 Then
oraclesave
End If

End Sub

 

[tweedle]

imo, there are too many conditions for us out here to evaluate what may be the circumstance in any given run.
Have you tried setting a breakpoint and stepping through to verify what conditions are being met?
Outside of that, I will sometimes start dropping Debug.? for all variables and which program flow was followed.

 

[Norie]

You definitely need to step through this code with F8.

One thing I don't quite get is why you create a new workbook and reference it using SaveWB but then appear to be trying to close the ActiveWorkbook.

Why not SaveWB.SaveAs?

That should make sure you close the correct workbook.

Also, in the saveprompt code there are 3 different subs possibly getting called, and one of those could get called twice.

 

[jroo]

I have stepped through this using F8. Just assume that the "Editcount" value is greater than 1.

In this block of code, the text for EditAlert, "There are edits..." isn't coming up half the time, prompting the user to either select Yes or No. I could see the user prompt box flicker on the screen quickly, but it defaults to Yes automatically, without allowing the user to select the choice, and continues through the macro in the "vbYes" part (which runs gotoCsEdits, then exits the sub procedure)... and as I mentioned before this runs fine when I run macro "editprompt" by itself, but when I click a button from my userform that calls the "editprompt" macro, the outcome isn't consistent.

Could it be my displayAlert option... or the fact that I named the "alert" EditAlert?

****

If Range("EditsCount").Value > 0 Then
EditAlert = "There are Edits. Would you like to review the Edit Report before saving to Oracle?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Edit Alert"

Answer = MsgBox(EditAlert, Style, Title, Help, Ctxt)

****

 

[tweedle]

I could see the user prompt box flicker on the screen quickly, but it defaults to Yes automatically...

Could be the DisplayAlerts

Help Says:

Remarks
The default value is True. Set this property to False to suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.

but that's not the result I get in 2007, possibly a prior version of Excel would provide such behavior.

Could try setting DisplayAlerts = True just before your msgbox, and set it back to False afterwards.

 

[tweedle]

I just re-read your initial post and it looks like you r already setting DA to true ....
at a loss...

 

[Norie]

Works fine for me, though I still don't follow most of the logic.

One thing that does seem to be missing is the variable savepath here:

ActiveWorkbook.SaveAs savepath

Also, just before that you turn off alerts.