Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Popping out Message Box! | Excel Discussion (Misc queries) | |||
How to stop message popping up before open the excel? | Excel Discussion (Misc queries) | |||
"Cannot empty clipboard" message keeps popping up | Excel Discussion (Misc queries) | |||
prevent Excel from popping-up an "OK" (information) message | Excel Programming | |||
broken links message still popping up | Excel Discussion (Misc queries) |