Message box

[goldcat]

I have to following code to copy date from one sheet to another and to pop up a message box to ask a question of the user ... the box doesn't appear.


help please

Application.ScreenUpdating = False

Sheets("Paid Outs").Select
Rows("6:26").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A6").Select
Sheets("Daily Reconciliation Sheet").Select
Range("G29:I48").Select
Selection.Copy
Sheets("Paid Outs").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("45:86").Select
Range("B45").Activate
Selection.Delete Shift:=xlUp

Range("A6:k105").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True

Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to Update Paidouts ?" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "Paidout Update" ' Define title.


If Response = vbYes Then ' User chose Yes.
MyString = Sheets("Paid Outs").Select
Range("A6").Select ' Activate the Paid out Sheet to work on.

Else ' User chose No.
MyString = Sheets("Daily Reconciliation Sheet").Select
Range("C4").Select ' Go back to the Imput Sheet.
End If

End Sub

 

[Jaafar Tribak]

I have to following code to copy date from one sheet to another and to pop up a message box to ask a question of the user ... the box doesn't appear.


help please

Application.ScreenUpdating = False

Sheets("Paid Outs").Select
Rows("6:26").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A6").Select
Sheets("Daily Reconciliation Sheet").Select
Range("G29:I48").Select
Selection.Copy
Sheets("Paid Outs").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("45:86").Select
Range("B45").Activate
Selection.Delete Shift:=xlUp

Range("A6:k105").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True

Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to Update Paidouts ?" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "Paidout Update" ' Define title.


'Add This...
Response =MsgBox(Msg,Style,Title)


If Response = vbYes Then ' User chose Yes.
MyString = Sheets("Paid Outs").Select
Range("A6").Select ' Activate the Paid out Sheet to work on.

Else ' User chose No.
MyString = Sheets("Daily Reconciliation Sheet").Select
Range("C4").Select ' Go back to the Imput Sheet.
End If

End Sub

 

[goldcat]

thanks I'll give that a try

 

[Richie(UK)]

Hi g,

You haven't defined what Response should be.

Like this:

Sub test()
Dim Msg As String, Style As Integer, Title As String, Response As Integer

Msg = "Do you want to Update Paidouts ?" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "Paidout Update" ' Define title.

Response = MsgBox(prompt:=Msg, Buttons:=Style, Title:=Title)

If Response = vbYes Then ' User chose Yes.
Else ' User chose No.
End If

End Sub

HTH


Edit: Doh. Too slow

 

[goldcat]

And why couldn't I see that (bangs head very hard on desk). maybe I should just go to bed !!!.

g

 

[goldcat]

Hi g,

You haven't defined what Response should be.

Like this:

Sub test()
Dim Msg As String, Style As Integer, Title As String, Response As Integer

Msg = "Do you want to Update Paidouts ?" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "Paidout Update" ' Define title.

Response = MsgBox(prompt:=Msg, Buttons:=Style, Title:=Title)

If Response = vbYes Then ' User chose Yes.
Else ' User chose No.
End If

End Sub

HTH

I must be really blonde tonite as I added the code you provided but I now get a compile error at the hilited point and I cannot see what the problem is the sheet spelling is correct

If Response = vbYes Then ' User chose Yes.
MyString = Sheets("Paid Outs").Select
Range("A6").Select ' Activate the Paid out Sheet to work on.

Else ' User chose No.
MyString = Sheets("Daily Reconciliation Sheet").Select
Range("C4").Select ' Go back to the Imput Sheet.
End If

 

[Ekim]

Same problem as previously. You have not defined “MyString” (at least it is not obvious from your code snippet that you have defined “MyString”) - just delete the reference i.e.

Change:
MyString = Sheets("Paid Outs").Select

To:
Sheets("Paid Outs").Select

Ditto for the following line that selects the "Daily Reconciliation Sheet” worksheet.

You could shorten your code a bit with the following:

If Response = vbYes Then ' User chose Yes.
Application.Goto Reference:=Worksheets("Paid Outs").Range("A6")

Else ' User chose No.
Application.Goto Reference:=Sheets("Daily Reconciliation Sheet").Range("C4")

End if


Regards,

Mike

 

[goldcat]

ray: thank you mike you are a life saver ray: