List Box Macro In Excel

[gregmw]

List Box Macro
I have a list box that has a macro attached to it so that when a selection is made from the list box the macro runs. However one of the options I have is when the selection is changed the user is asked if they are sure they want to change to the new selection. If they input Yes then the macro runs and the list box selection changes. If they decide they don't want to change the selection the macro does not run. However the selection still changes. Does any one have a simple idea to keep the selection at it's orginal value if the user selects No.

Thanks

 

[Stretchwickster]

Why not use something like this on entry into your macro code:

If MsgBox("Do you want to continue with changes?", vbYesNo) = vbYes Then
    'user clicked "yes" so make changes
  Else
    'user clicked "no" so don't make changes
  End If

Clive

http://www.kucu.co.uk

 

[RobBroekhuis]

Keep track of the previous selection, and restore it when needed. Something like:

sub listbox_change
static oldchoice as string
if msgbox.....=vbYes then
...run macro...
oldchoice=listbox
else
listbox=oldchoice
end if
end sub

Rob

 

[gregmw]

These ideas will not work as the way a macro runs on a list box is it only activates when you have changed the selection.

 

[RobBroekhuis]

I don't see why the code I suggest would not work. Yes, the macro is called when the selection has been changed - but if the user answers "no", then the selection is returned to its previous value. You probably need to insert
application.enableevents=false
...
application.enableevents=true
around the listbox=oldchoice statement, to prevent the change event from firing when the listbox is changed by that line of code.

You may also need to put some code in the worksheet_activate or workbook_open event to initialize the oldchoice variable (in which case you need to pull the variable out of the sub and make it public), if the listbox can have a value to start with.

Rob

 

[gregmw]

Rob thanks for trying to help imagine if you will I have a list box with 3 selections available here is the simplified macro attached (with your extra bits of code)

Sub ModifyS()

Static oldchoice As String

Replace = Application.InputBox("Warning you will replace the data entered on this sheet! Enter ""Y"" to continue&quot
If Replace = "Y" Then

'rest of macro not here

oldchoice = ListBox

Else
Application.EnableEvents = False

ListBox = oldchoice
Application.EnableEvents = True

End If

End Sub


This still does not change the selection back to the original value if the user cancels the first message box. am I not writing the code correctly?
Thanks Greg

 

[RobBroekhuis]

My guess is that it works the SECOND time, but not the first time the user changes the selection (i.e., after the first time the user responded "yes&quot? In that case, you'll have to implement what I suggested in my second post, that is:
..in the declaration section of a regular code module..
public oldchoice as string

..in the workbook code module..

sub workbook_open
oldchoice= (listbox value. how to reference depends on how the listbox was created)
end sub

..and then of course..
sub listbox_change
if msgbox.....=vbYes then
...run macro...
oldchoice=listbox
else
application.enableevents=false
listbox=oldchoice
application.enableevents=true
end if
end sub

Rob