message box popping up when the selection of a Form combo box changes
 

All:

I have a Form Combo Box. There are three option inside the box for me
to select. Definitely, one option will be visible and the other two
options will be Invisible unless I click the combo box. What I am
trying to do is whenever I click the combo box and select one option
which is different from the PREVIOUS VISIBLE one, I want a simple
message of "Are you sure you want to switch the selection"?

Please advise!

message box popping up when the selection of a Form combo box chan
 

You said a Form Combo Box. I have assumed that you mean one that was created
from the Forms Tool bar and not one created from the Control Tool Box toolbar.

You need to have a linked cell which displays the number of any current
selection plus an additional cell on your worksheet to save the selection
each time it is changed. These can be anywhere out of site on the worksheet.

If you have not already got one then you need to set up the linked cell in
the combo box format control. You then need to initialize the other cell to
save the current selection by simply typing in the number to match the linked
cell. Note: A Forms Combo Box linked cell displays a number relating to the
selection, not the actual selection.

Then you could use a macro something like this:

Sub DropDown2_Change()
Dim msge
Dim style
msge = "Are you sure you want to switch the selection?"
style = vbYesNo

Response = MsgBox(msge, style)

If Response = vbYes Then
'Save the selection to a cell on worksheet
Sheets("Sheet1").Range("C1") = Sheets("Sheet1").Range("B1")
'At this point you may call another
'procedure to run because of the change
'or do nothing
Else
'Revert to previous selection. Changing the linked cell number
'changes the selection in the Combo Box.
Sheets("Sheet1").Range("B1") = Sheets("Sheet1").Range("C1")
Exit Sub 'Terminate any further processing
End If
End Sub


Regards,

OssieMac

"George" wrote:

All:

I have a Form Combo Box. There are three option inside the box for me
to select. Definitely, one option will be visible and the other two
options will be Invisible unless I click the combo box. What I am
trying to do is whenever I click the combo box and select one option
which is different from the PREVIOUS VISIBLE one, I want a simple
message of "Are you sure you want to switch the selection"?

Please advise!

message box popping up when the selection of a Form combo box chan
 

Hi, OssieMac:

You are exactly right.

Firstly, the combo box was one created from the Forms Tool bar and not
one created from the Control Tool Box toolbar.
Secondly, I already set the combo box to be linked to cell B1 (index
of the selection from the combo box).
Finally, whenever I change the selection from the combo box, I just
want a WARNING message popping up and the response to the message is
always "ok". In other words, if I click the combo box but I don't
change the PREVIOUS selection, I DON'T need the message box.

If you can modify your code, it will be great. Your efforts are highly
appreciated!

Thanks again,

message box popping up when the selection of a Form combo box
 

Hi George,

I wonder what the problem is. Maybe different versions of Excel or maybe we
are on a different wave length.
What version of Excel are you running?
Do you already have a macro assigned to the combo box? If so, the code I
have given you will have to be integrated into it.
Do you understand then you can't stop the change taking place when you click
on an alternative name, you can only handle the change and revert to the
original if answer to question is no?

The code appears to work fine in version 2002. It should not run if you
simply click on the combo box. It should only run if you make a change to the
selection in the combo box and you then you have to answer Yes or No to the
question. If Yes, the change is allowed. If No, then the change not allowed
and it reverts to the previous selection.

I have assumed that you know how to copy the macro into the module via the
VBA editor and assign it to the combo box. If not then let me know and I'll
give you further instructions. If you copy it into the module, you can change
the sub name to anything you like but you have to right click on the combo
box and assign the combo box to the particular macro.

Regards,

OssieMac

"George" wrote:

Hi, OssieMac:

You are exactly right.

Firstly, the combo box was one created from the Forms Tool bar and not
one created from the Control Tool Box toolbar.
Secondly, I already set the combo box to be linked to cell B1 (index
of the selection from the combo box).
Finally, whenever I change the selection from the combo box, I just
want a WARNING message popping up and the response to the message is
always "ok". In other words, if I click the combo box but I don't
change the PREVIOUS selection, I DON'T need the message box.

If you can modify your code, it will be great. Your efforts are highly
appreciated!

Thanks again,

message box popping up when the selection of a Form combo box
 

Hi, OssieMac:

Mr. Merjet wrote me a very short code which is almost perfect except
one problem. The code is as follows:

Sub DropDown1_Change()
Static iPrev As Integer
Dim iRtn As Integer
iRtn = MsgBox("Are you sure you want to switch the option?",
vbYesNo)
If iRtn = vbNo Then 'C4 is linked cell
ActiveSheet.Range("C4") = iPrev
Else
iPrev = ActiveSheet.Range("C4")
End If
End Sub


Here is the problem. I saved my excel file and closed up. When I re-
open it, click the combo box and click another option other than the
current one, a message comes up asking me "Are you sure you want to
switch the option?". If I click "No", the combo box will be blank as
it is not supposed to be because I want nothing to be changed whenever
I click "No" as a response to the message.

Do you have any ideas how to fix this? This code is really neat!

Thank you so much and I look forward to hearing from you soon!

message box popping up when the selection of a Form combo box
 

George,

The basis of Mr. Merjet's example works exactly the same way as the code
that I gave you except for the fact that Mr. Merjet's will not work after you
close Excel and re-open it because it loses the value of iPrev which only
remains 'Static' as long as Excel is open. (It would work if the first time
after opening the workbook you actually changed the selection and answered
Yes to keep it because it would reinitialize iPrev.)

If you use the example I gave you and initialize cell C1 to whatever value
is in the linked cell (you only have to do this once) then the code I give
you works the same way as Mr. Merjet's but does not lose the value when you
close the workbook and Excel.

Regards,

OssieMac

"George" wrote:

Hi, OssieMac:

Mr. Merjet wrote me a very short code which is almost perfect except
one problem. The code is as follows:

Sub DropDown1_Change()
Static iPrev As Integer
Dim iRtn As Integer
iRtn = MsgBox("Are you sure you want to switch the option?",
vbYesNo)
If iRtn = vbNo Then 'C4 is linked cell
ActiveSheet.Range("C4") = iPrev
Else
iPrev = ActiveSheet.Range("C4")
End If
End Sub


Here is the problem. I saved my excel file and closed up. When I re-
open it, click the combo box and click another option other than the
current one, a message comes up asking me "Are you sure you want to
switch the option?". If I click "No", the combo box will be blank as
it is not supposed to be because I want nothing to be changed whenever
I click "No" as a response to the message.

Do you have any ideas how to fix this? This code is really neat!

Thank you so much and I look forward to hearing from you soon!

message box popping up when the selection of a Form combo box
 

OssieMac, you are correct. PrivateIy I sent sent George a revision
after he brought the matter up. It works like yours, using another
cell as a "linked cell backup."

Merjet