combo box change event & case select

E

Excel-General

Private Sub ComboBox2_Change()
On testing this macro for the "exit" case when the combo box of the
form reads "exit" it doesn't clear the value, nor does it unload/exit
at least it doens't seem to. I tried it with a click event instead of
change event and it does the same thing. The form just hangs until I
press the red close button and then it goes back to the load script.

On Error Resume Next
Select Case ComboBox2.Value

Case Is = ComboBox2.Value = "All"
cpyAllpatientShts
ComboBox2.Value.Clear
Unload Me
Case Is = ComboBox2.Value = "Exit"
ComboBox2.Value.Clear
Unload Me
Case Is = ComboBox2.Value <> ""
ComboBox2.Value = g_fName
CpySinglePatientSht

ComboBox2.Value.Clear
Unload Me
End Select
On Error GoTo 0
End Sub
 
D

Dave Peterson

It's usually bad practice to hide errors with "on error resume next" lines. You
can never know why things are failing.


Select Case ComboBox2.Value
Case Is = "All"
cpyAllpatientShts
Case Is ="Exit"
'do nothing
Case else
'this next line could cause problems
'it'll make the combobox2_change event fire again
'I'm not sure why you're doing it, though, since you're unloading
'the userform later
'maybe you could just use another variable--not a control on the
'userform
'ComboBox2.Value = g_fName
CpySinglePatientSht
End Select

Unload me


======
Untested, uncompiled.
 
E

Excel-General

Well I can leave the else off but there are 3 choices, they can pick 1
patient name to print, all the patients names to print or they can
exit. The combo box is a drop down list.

Option Explicit
Public g_rng As Range
Public g_fName As String

Public Sub ComboBox2_change()

On Error Resume Next
Select Case ComboBox2.Value

Case Is = ComboBox2.Value = "All"
cpyAllpatientShts

Case Is = ComboBox2.Value = "Exit"
'do nothing

Case Is = ComboBox2.Value <> ""
ComboBox2.Value = g_fName
CpySinglePatientSht

End Select

Unload Me
On Error GoTo 0
End Sub
 
D

Dave Peterson

You put the "on error resume next" back.

You changed all the "case is =" lines back.

You put the "ComboBox2.Value = g_fName" line back.

I don't know what else to suggest.
 
E

Excel-General

You are right about it being hard to find an error .
I have another pressing question. This is on the userform related
code so I declared it a global variable because I want to call the
months script in the module related to the sheet.
On this script you notice I declared the global variable fname:
but you notice the months script? That script doesn't compile because
it thinks I didn't declare the global fname variable which I did
declare in the userform. Do I also have to declare it in the module
in order to use it? The error says variable not defined. So my
question is do you have to define the same variable on the userform
and module? What I want is the selected name in the list to get
copied into a variable then printed in the months script.
Option Explicit
Public g_rng As Range
Public g_fName As String



Public Sub ComboBox2_change()

On Error Resume Next
Select Case ComboBox2.Value

Case Is = ComboBox2.Value = "All"
cpyAllpatientShts

Case Is = ComboBox2.Value = "Exit"

'do nothing

Case Is = ComboBox2.Value <> ""
g_fName = ComboBox2.Value
CpySinglePatientSht
months
End Select

Unload Me
On Error GoTo 0
End Sub




Public Sub months()
Dim inputDate As Date
Dim i As Long

inputDate = InputBox("Enter a date:", "Date", Date)
For i = 2 To Worksheets.Count
Sheets(i).Range("v6") = inputDate
Sheets(i).Range("A4") = g_fName
Next i

End Sub
thank you in advance,
 
D

Dave Peterson

What happened when you tried the suggestions I offered? You may want to try it.

You can put the declaration in a General module or as long as the userform is
loaded, you could refer to it as

userform1.g_fName

(change the userform1 to match the name of your userform.)
 
E

Excel-General

What happened when you tried the suggestions I offered? You may want to try it.

You can put the declaration in a General module or as long as the userform is
loaded, you could refer to it as

userform1.g_fName

(change the userform1 to match the name of your userform.)
Click to expand...

I did try it, I guess I lost the changes.
 
E

Excel-General

What happened when you tried the suggestions I offered? You may want to try it.

You can put the declaration in a General module or as long as the userform is
loaded, you could refer to it as

userform1.g_fName

(change the userform1 to match the name of your userform.)
Click to expand...

that is really nice, I love naming conventions. I will have to look
on microsoft mvps for naming conventions.
 
E

Excel-General

What happened when you tried the suggestions I offered? You may want to try it.

You can put the declaration in a General module or as long as the userform is
loaded, you could refer to it as

userform1.g_fName

(change the userform1 to match the name of your userform.)
Click to expand...

It appears to be the file, everytime I open it Excel opens every file
my personal macro and some backups of this file and 2 or 3 versions of
them. they aren't even in the same folder. I will just watch for it.
 
E

Excel-General

You are right, but I was concerned about the part that you say can
give unexpected error s and didn't understand the logic of it. So
you are saying that is how a case statement works, there has to be an
else at the end for all other cases? Okay, I take your word for it.
We won't worry about the unexpected problems since this script is only
heavily used every 6 months. Thanks,

case 1
do somethine else
case 2
do something
else
do a 3rd thing

is that because
 
E

Excel-General

You put the "on error resume next" back.

You changed all the "case is =" lines back.

You put the "ComboBox2.Value = g_fName" line back.

I don't know what else to suggest.
Click to expand...



I took the on error resume off however my thought was some error
checking is better than no error checking.
 
D

Dave Peterson

Your code is unusual, but it would work (but I still wouldn't use it).

Case Is = ComboBox2.Value = "All"

If the combobox's value is "All", then this line of code will evaluate to:

case is = true
Combobox2.value = "All" will be evaluated first and in this case, it will
evaluate to true.

But it's not the usual syntax for this kind of thing.
 
D

Dave Peterson

Using "on error resume next" isn't really any error checking. It ignores any
and all errors. I would expect even more unanticipated problems by using this
kind of thing.
 
D

Dave Peterson

I don't understand what this means.

It appears to be the file, everytime I open it Excel opens every file
my personal macro and some backups of this file and 2 or 3 versions of
them. they aren't even in the same folder. I will just watch for it.
Click to expand...
 
E

Excel-General

You put the "on error resume next" back.

You changed all the "case is =" lines back.

You put the "ComboBox2.Value = g_fName" line back.

I don't know what else to suggest.
Click to expand...

I guess you are right about the error message you should at least
leave it off while writing it to see if there really are errors.
thanks,I just copied it from something else I wrote without thinking
about it.
 
E

Excel-General

What happened when you tried the suggestions I offered? You may want to try it.

You can put the declaration in a General module or as long as the userform is
loaded, you could refer to it as

userform1.g_fName

(change the userform1 to match the name of your userform.)
Click to expand...

Hey Dave
It did iterate through the whole process now and works pretty well
however
I did it as suggested and the else part didn't work. The single value
scripts didn't execute it unloaded.

Public Sub ComboBox2_change()


Select Case ComboBox2.Value

Case Is = ComboBox2.Value
Case Is = "All"
cpyAllpatientShts

Case Is = "Exit"
ComboBox2.Value = "Exit"

'do nothing

Case Else

g_Fname = ComboBox2.Value
CpySinglePatientSht
months
End Select

Unload Me

End Sub
 
E

Excel-General

What happened when you tried the suggestions I offered? You may want to try it.

You can put the declaration in a General module or as long as the userform is
loaded, you could refer to it as

userform1.g_fName

(change the userform1 to match the name of your userform.)
Click to expand...

No Dave it seems to be working pretty well, I forgot the exit sub.
I'm sorry for all the posts i guess I overdid Google groups. Have a
nice weekend. THANKS,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ComboBox with VBA 0
Combo Boxes 2
question on scope of a global variable 1
What is wrong with drop-down box? 7
Combo Box 13
select case statement? 5
linked comboboxes change on delete 3
combo box based on a second combo box not working 2

Top