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!