msgbox 2

[max1565]

I have the following:

MsgBox "DO YOU WANT TO SAVE THIS FORM?", vbYesNo
If vbYes Then
Sheets("DATABASE&quot.Range("A2&quot = "AGENT"
'Application.Run "FORM.xls!SAVE"
ElseIf vbNo Then
Sheets("DATABASE&quot.Range("A2&quot = "MAX"
End If

Even when i click no it still does the case for yes. Could someone tell me what is missing?

Thanks

 

[dbsquared]

you need to set it to a variable:

temp= MsgBox "DO YOU WANT TO SAVE THIS FORM?", vbYesNo

If temp = vbYes Then
Sheets("DATABASE&quot.Range("A2&quot = "AGENT"
'Application.Run "FORM.xls!SAVE"
ElseIf temp = vbNo Then
Sheets("DATABASE&quot.Range("A2&quot = "MAX"
End If
That should work for you

To go where no programmer has gone before.

 

[max1565]

Thanks for the quick reply. It is now giving me a syntax error for the following line

temp= MsgBox "DO YOU WANT TO SAVE THIS FORM?", vbYesNo
Thanks

 

[dbsquared]

sorry like this

temp= MsgBox ("DO YOU WANT TO SAVE THIS FORM?", vbYesNo)
that is what you want the () for the msgBOX


To go where no programmer has gone before.

 

[DaleWatson123321]

Max,

Here's an additional bit of help - that I hope you and others will take very seriously when it comes to writing VBA code that references cells anywhere in the workbook.

With the example you provided, it suggests you haven't yet begun to use (create) "range names". By referencing cell coordinates in VBA code, it means that whenever changes are made - such as inserting/deleting rows/columns or moving data from place to place - the VBA code will also need to be changed.

By creating and using range names, on the other hand, this will, for the most part, eliminate the need to modify your VBA code. This is because "internally" Excel maintains a link between each range name and the cell(s) to which the name is assigned.

In your example, therefore, you could modify it as follows...

If temp = vbYes Then
Range("ans&quot.Value = "AGENT"
'Application.Run "FORM.xls!SAVE"
ElseIf temp = vbNo Then
Range("ans&quot.Value = "MAX"
End If

Note that the range name "ans" can be assigned to any location in the workbook - without the need to reference the worksheet.

While there are different methods of creating range names, the method I recommend is...
a) Highlight the cell (or range of cells)
b) Hold down <Ctrl> and hit <F3>
c) Type the name
d) Hit <Enter>

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca