MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Could someone tell me what's wrong with this code?

Posted by Cory on July 02, 2001 7:24 PM

The following return a the second condition of a "No" msgbox:

Private Sub lblCloseall_Click()
Dim response
MsgBox "If you haven't saved your work you will be prompted to do so upon exiting. Would you like to coninue?", vbYesNo, "Quitting?"

If reponse = vbYes Then
MsgBox "You chose, ""yes"""
MsgBox "You chose, ""No"""
End If
End Sub

And this code gives me a runtime error 380 saying it cant set the text:

Private Sub cboSOpen_Exit(ByVal Cancel As MSForms.ReturnBoolean)
cboSOpen.Text = Format(cboSOpen.Text, "hh:mm AMPM")
cboSOpen.Value = Sheet4.Range("b3").Text
End Sub

Posted by Ivan F Moala on July 02, 2001 7:55 PM

Always a good idea to set ALL your projects with
Option explicit
From help files;
When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.

If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.

Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.

If you had Option explicit @ the top of your
module it would have picked up the error below.
Your 1st routine has a mispelled declaration
so it always falls through to VBno.

Your 2nd Q...not sure not enought info....
What exactly are you trying to do...and what is
in sheet4 rangeB3


Posted by Cory on July 03, 2001 6:37 AM

For the first one I understand using option explicit as a tool to trap and pinpoint errors, but I can't find the misspelling of the declaration that lets it fall through to vbNo.

As for the second one, I was trying to capture what the user selected with the combobox and place that selection in the cell sheet4.range("b3"). Since then I've figured out that I don't need the ".text", and I've also noticed instead of using the sheet.range syntax (which gives me an error), I have to activate the desired sheet first then just refer to it's cell:

range("b3") = combobox1.value

My problem was excel wasn't accepting the "Format" code which was trying to make what was selected in the combobox display as a time, ie: 8:00 AM instead of : 0.8333333333334

I appreciate the help, Ivan.

Posted by Michele on July 05, 2001 6:30 AM

I think your repoblem is that it doesn't know what "reponse" is. I believe you should have:
Response = Msgbox ("msg",vb..,"Quitting")