Solved: MsgBox problem..

[Mr.G]

Hi all
Could someone help with this msgbox. I have this code that is a selection of Option buttons now if there is no option selected I want it to display the msgbox ,but what happens now is when you have gone through a couple of these Frms that have options in them and you exit,all the msgboxes from all the frms display one after another.
What am I doing wrong?? :dunno


Private Sub CmdHyes_Click()

FrmSel.lRqua = TxtQR

If OpH Then
FrmSel.lR.Caption = lH
Unload Frm6Hris
FrmEmpty.Show
End If

If OpHC Then
FrmSel.lR.Caption = lHC
Unload Frm6Hris
FrmEmpty.Show
End If

If OptionButton = False Then GoTo Msg
Exit Sub
Msg:
MsgBox "Please make a selection"

End Sub

Pls. help.

[Simon Lloyd]

When you say "exit" how are you exiting the forms? you could try Application.EnableEvents = false prior to running your MsgBox and then turn it back on before your end sub.

Im not sure on this....maybe someone else will be prompted in to giving you a better solution.

Regards,
Simon

[moa]

Try to not use the "goto... exit sub" way of programming. I was always told it was bad programming and I'm pretty sure it's what's wrong with your code. Find a way around it even if it means repeating a line of code (your message box code).

Can you have both OpH and OpHC be TRUE at the same time? if not use an if, elseif, else structure.

[Mr.G]

Sorry I'm fairly new to forum and VBE. You can only select one option at a time.
Could you help explain what you just said moa?

[moa]

I re-read your post and I'm not sure what you want to do with this code but if you look at the flow of the subroutine it goes through the three if statements then the message box code runs regardless of what goes on before it. Having ":Msg" above the message box code does not mean it will run only if you use "goto Msg". If you only wanted the message box to pop up after checking that the option button value is true then...


If OptionButton = False Then
MsgBox "Please make a selection"

End If


HTH

[moa]

Forgive me I am tired, did not even notice where you had placed Exit Sub.

[lucas]

As you can see.....everyone is guessing at what is going on with your userform.......post it so they can help you....

lots of questions that can be cleared up with a posted workbook example. For instance are you using frames with your option buttons?

click post reply then scroll down till you find "manage attachments"

[Mr.G]

Does this help?
There is more than one Frm that has option button on it.For various projects.
The Frm in the background runs vb(I think that is what you call it)It just captures the selections that was inserted by the user.

Now when nothing is selected or inserted on any Frms it shouldn't run the next frm but give you a msg that instructs you to fill in the missing info.

[johnske]

I can't find any code or forms in your attachment...

You have a procedure that - in essence - is calling another procedure. Now, even though the 1st form has been unloaded, the procedural code that resides in it is still running (it hasn't reached Exit or End Sub, it's just called another procedure), so if you (say) cancel the 2nd form without making a choice, you'll get a msgbox from the procedure in the 2nd form, then the code in the 1st procedure will continue to its conclusion and also give you a msgbox.

Hope that explains your problem :)

[Mr.G]

Should I upload the original doc??
Like I say I'm new ,and teaching myself VBE.
Please be patient.
moa's solution worked for me but if the rest would like to help with achieving my goal it would teach me a great deal. This is like a facinating new world ,and I'm currently hooked.

[johnske]

Should I upload the original doc??...Yes, that's the easiest course, get rid of anything personal and upload. It's difficult to duplicate what you already have (so as to test it) without seeing it. :)

[johnske]

...moa's solution worked for me but if the rest would like to help with achieving my goal it would teach me a great deal. This is like a facinating new world ,and I'm currently hooked.Re this edit - yes, what moa has said is correct (the reasoning behind this was what I was trying to explain in my previous post with respect to your original code - the flow of logic needs to be re-ordered)

[Mr.G]

Feel free.This is what I've got.

[johnske]

OK, without seeing the attachment your original code (in post #1) was really quite enigmatic.

I now see it is intended for the form you've named Frm6Hris (but I see you've changed that in the attachment), and this code is to be used in conjunction with the option buttons you've re-named to OpH and OpHC.

First, you don't need to say Unload Frm6Hris, the code resides in Frm6Hris so you only need to say Unload Me. We can thus rewrite your original code in the following form...

Private Sub CmdHyes_Click()
FrmSel.lRqua = TxtQR
If OpH Then
FrmSel.lR.Caption = lH
Unload Me
FrmEmpty.Show
ElseIf OpHC Then
FrmSel.lR.Caption = lHC
Unload Me
FrmEmpty.Show
Else
MsgBox "Please make a selection"
End If
End Sub

You can do this throughout (adding more ElseIf's where required...)

Also, enigmatic code names like those below con't help you or anyone else that may be trying to debug your code, i.e.

Frm1SS
Frm2DS
Frm3TS
Frm4FUorR
Frm7FH
Frm7H

you'd be better off using some plain descriptive English that's similar to the caption on the form, for example, you could use something like

Frm1SS > Frm1StoryOfficeOrHouse or Form_1StoryOfficeOrHouse
Frm2DS > Frm2StoryOfficeOrHouse or Form_2StoryOfficeOrHouse
Frm4FUorR > Frm4UnitsOrRiser or Form_4UnitsOrRiser
etc.

These longer names don't increase the overhead unduly and they make your code easier to follow.

Why are you using ScreenUpdating = True in so many places when you have not set ScreenUpdating = False? (BTW, you need to use Application.ScreenUpdating = True)

You do not use Option Explicit at the head of your code modules, Visual Basic must then make a 'best guess' as to what you mean, sometimes that will work out the way you intend and sometimes it won't, and, if no sense can be made of it, it's then just ignored without you even being aware of it.

Use Option Explicit and explicitly state what your variables are, what type they are, and what you want done with them. Then click Debug > Compile VBA Project to see how many 'errors' you have and sort them out.

Things like this...
Private Sub Eststrt_AfterUpdate()
NumberFormat = "[$-C09]dd-mmmm-yyyy;@"
End Sub
will then be highlighted (just what are you trying to apply this number format to? - You must state it).

Also, use comments liberally, remember that someone may be called on to debug your code at some time in the future - if you're unlucky that will be you - in a couple of months (maybe weeks) you'll look at the code you've written now and go "??WHAT the...??" :)

HTH

[Mr.G]

Thanx
My option explicit gets set automaticaly.I do not know why.
Would this be the correct way to do the number formatting for the Lbl?


Private Sub Eststrt_AfterUpdate()
Eststrt = "[$-C09]dd-mmmm-yyyy;@"
End Sub

[johnske]

Thanx
My option explicit gets set automaticaly.I do not know why.
Would this be the correct way to do the number formatting for the Lbl?


Private Sub Eststrt_AfterUpdate()
Eststrt = "[$-C09]dd-mmmm-yyyy;@"
End Sub
No, you still have to use the NumberFormat property, it's just that you always need a qualifier for where this format is to be applied, try, e.g. Eststrt.NumberFormat = "[$-C09]dd-mmmm-yyyy;@"

[Mr.G]

Could I ask about this aswell.
How do I get the value of the dropdown box eg.Pex with a value of 5.00E07 to display in the text box?

[Bob Phillips]

Private Sub CBoxPipeType_Change()
TxtPipeype.Text = Application.VLookup( _
CBoxPipeType.Value, Range("D8:E11"), 2, False)
End Sub

[Mr.G]

Thnx
That works