MsgBox Problem 1

[jisoo22]

Hello! =)

I'm trying to add a msgbox function into my excel macro where it would pop up specifically when a cell in my spreadsheet is empty instead of filled in. Here's a piece of the script:

Msg = Msg & Range("B4&quot.Value & "^"
If Range("B4&quot.Value = "" Then
MsgBox("You must enter a number!", vbOKOnly, "STOP!&quot = vbOK
Stop
Else
Msg = Msg & Range("B5&quot.Value & "^"



When I try to run this, I get a compile error that says, "Function call on left-hand side of assignment must return Variant or Object". Does anyone have a solution?

Thanks,
Jisoo22

 

[jonscott8]

MsgBox("You must enter a number!", vbOKOnly, "STOP!&quot = vbOK

It is trying to assign vbOK to MsgBox("You must enter a number!", vbOKOnly, "STOP!&quot

I think you either forgot the IF condition:
IF MsgBox("You must enter a number!", vbOKOnly, "STOP!&quot = vbOK THEN

or you dont need a return value (which I think is the case):
MsgBox "You must enter a number!", vbOKOnly, "STOP!" Jon Hawkins

 

[jisoo22]

The thing is that when my macro parses through the cells and sees that cell "B4" is empty, I want a dialog box to pop up and tell the user to enter something into it and then halt the entire process so that he or she will be able to do it. So basically in psudocode:

If B4 is empty then
a message box appears and when they hit ok or cancel
the macro is halted.
Else the macro continues.
End If


Hope this gives a better idea =)

Thanks,
Jisoo22

 

[jisoo22]

I may have an additional problem too. No matter what variation I put in, the macro acts as if the msgbox function did not exist and just operates normally. Could this be a result of Excel exporting information out to another application(Outlook)? And if so, is there a way to adjust the msgbox function appropriately? I tried to change it's modality but even that didn't work.

Thanks,
Jisoo22

 

[jisoo22]

If this makes it easier, here's the entire code =)


Dim x As Double
' Get the email address
Email = "email address here"

' Message subject
Subj = "Warranty Validation Registration"

' Did they fill out the appropriate fields?
If Range("B4&quot.Value = "" Then
MsgBox "You must enter a number!", vbOKOnly + vbSystemModal, "STOP!"
End If
' Compose the message
Msg = ""
Msg = Msg & Range("B2&quot.Value & "^"
Msg = Msg & Range("B4&quot.Value & "^"
Msg = Msg & Range("B5&quot.Value & "^"
Msg = Msg & Range("B6&quot.Value & "^"
Msg = Msg & Range("B7&quot.Value & "^"
Msg = Msg & Range("F4&quot.Value & "^"
Msg = Msg & Range("F5&quot.Value & "^"
Msg = Msg & Range("F6&quot.Value & "^"
Msg = Msg & Range("F7&quot.Value & "^"
Msg = Msg & Range("A9&quot.Value & "^"
Msg = Msg & Range("B9&quot.Value & "^"
Msg = Msg & Range("C9&quot.Value & "^"
Msg = Msg & Range("D9&quot.Value & "^"
Msg = Msg & Range("E9&quot.Value & "^"
Msg = Msg & Range("F9&quot.Value & "^"
Msg = Msg & Range("G9&quot.Value & "^"
Msg = Msg & Range("H9&quot.Value & "^"
Msg = Msg & Range("A11&quot.Value & "^"

For i = 0 To 23
For j = 0 To 1
Range("A17&quot.Select
ActiveCell.Offset(i, j).Select
Msg = Msg & Selection.Value & "^"
Next
Next

For a = 0 To 23
For b = 0 To 1
Range("E17&quot.Select
ActiveCell.Offset(a, b).Select
Msg = Msg & Selection.Value & "^"
Next
Next

Msg = Msg & Range("B41&quot.Value & "^"
Msg = Msg & Range("D41&quot.Value & "^"
Msg = Msg & Range("B42&quot.Value & "^"
Msg = Msg & Range("G43&quot.Value & "^"
Msg = Msg & Range("G44&quot.Value & "^"
Msg = Msg & Range("G45&quot.Value & "^"
Msg = Msg & Range("G46&quot.Value & "^"
Msg = Msg & Range("G48&quot.Value & "^"
Msg = Msg & Range("A47&quot.Value & "^"
Msg = Msg & Range("B47&quot.Value & "^"
Msg = Msg & Range("C47&quot.Value & "^"
Msg = Msg & Range("E47&quot.Value & "^"

Set myOlApp = CreateObject("Outlook.Application&quot
Set myItem = myOlApp.CreateItem(olMailItem)
With myItem
.Subject = Subj
.Body = Msg
End With
myItem.Recipients.Add (Email)
myItem.Send

End Sub

 

[DarkSun]

Instead of using a Msgbox why don't you use an Inputbox.

Example:

If Range("B4&quot.Value = "" Then
MyAnswer = ""

Do While MyAnswer = ""
MyAnswer = InputBox("You must enter a number!", "Title&quot
Loop

Range("B4&quot.Value = MyAnswer
End If

 

[jisoo22]

Thanks but I tried something like that too, unfortunately I get the same result. The macro still parses through the entire form without even stopping. No dialog/input boxes ever pop up. I believe this could lie in the fact that the macro does not pick up the empty cell as simply "" but rather a different value. I tried the IsNull() and IsEmpty() functions also as follows, but to no avail.

If IsNull(Range("A9&quot.Value) = True Then
MyAnswer = InputBox("You must enter a dealer number!", "Title&quot
Range("A9&quot.Value = MyAnswer
End If


This acted as if it were invisible also, no error messages or anything. Anyone have any ideas?

Thanks,
Jisoo22

 

[jisoo22]

Ok, I think I have this figured out partially. I found that the IsNumeric statement works, but only partially. Apparently I need some kind of "onFocus" type of function on the cell itself. Someone told me that a statement called select would do the trick. Does anyone know how I can fit that in? Here's the section of code that works:


If IsNumeric(Range("B4&quot.Value) = False Then
MyAnswer = InputBox("Please enter the dealer's number.", "Stop&quot
Range("B4&quot.Value = MyAnswer
End If
[/i}

Thanks!
Jisoo22

 

[Kaah]

Jisoo,

do you know about the "exit sub" method? Depending on if your code is in a function or sub, enter "exit function" or "exit sub" after displaying the msgbox. This will stop the program from going any further.

ie

' Did they fill out the appropriate fields?
If Range("B4&quot.Value = "" Then
MsgBox "You must enter a number!", vbOKOnly + vbSystemModal, "STOP!"
Exit Function
End If


make sure the exit statment is enclosed in the if-end if otherwise te program will stop every time! Hope this helps.
Darksuns input box idea was muc better though, but to make that work you'll need to either use te exit statement, or even better, set up a loop-until statement and a seperate numeric variable to contain the result. The beauty (sorry darksun, maybe you wanted to talk about this?) of darksuns approach is that once you've set up the loop, you can then do some data validation to make sure that what is entered is actually numeric and within any other boundaries that you may set. Using the loop, te user will be continuously prompted for a correct value until it meets the criteria set out by you, or they hit cancel.

Answer back if you want code for this.

Kaah