Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox after change but user hits cancel to message box
the code below is used to save some data when a user changes months on a
combobox. The code prompts the user to see if he/she wants to save the estimates with a Yes, No, Cancel. yes and no cases work as expected but when the user clicks cancel, the code exits out as expected but the combox box value is now set to the new month the user selected. I would like it to be set back to the month they had in the combox before the changed it. how can I do modify the code to do that or is there a better way to do this? thanks Private Sub cboMonth_Change() Dim myCheck As Integer Dim ms As String Dim dc As Long On Error Resume Next Application.ScreenUpdating = False ms = Format(Range("cyMonthSave"), "mmm") With Sheets("Estimates") 'finds appropriate column dc = .Rows(1).Find(What:=ms, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column End With If Sheets("Merchandise Store Plan").Range("m15").Value = Sheets("Estimates").Cells(60, dc).Value Then GoTo SkipChanges: End If myCheck = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Save") If myCheck = vbCancel Then Exit Sub Else If myCheck = vbNo Then GoTo SkipChanges: End If End If mcrSaveEstimates 'No changes to save SkipChanges: Range("A2") = Sheets("Misc").Range("CYMonth").Value Range("A3") = Sheets("Misc").Range("PYMonth").Value 'brings back previosly saved estimated for the new month selected RestoreEstimates Application.ScreenUpdating = True Beep End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox after change but user hits cancel to message box
Hi
Why have a cancel? regards Paul On Mar 5, 3:02*pm, VBANovice wrote: the code below is used to save some data when a user changes months on a combobox. *The code prompts the user to see if he/she wants to save the estimates with a Yes, No, Cancel. yes and no cases work as expected but when the user clicks cancel, the code exits out as expected but the combox box value is now set to the new month the user selected. *I would like it to be set back to the month they had in the combox before the changed it. * how can I do modify the code to do that or is there a better way to do this? thanks Private Sub cboMonth_Change() Dim myCheck As Integer Dim ms As String Dim dc As Long On Error Resume Next Application.ScreenUpdating = False ms = Format(Range("cyMonthSave"), "mmm") With Sheets("Estimates") 'finds appropriate column dc = .Rows(1).Find(What:=ms, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column End With If Sheets("Merchandise Store Plan").Range("m15").Value = Sheets("Estimates").Cells(60, dc).Value Then GoTo SkipChanges: End If myCheck = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Save") If myCheck = vbCancel Then * * Exit Sub * *Else * *If myCheck = vbNo Then * * *GoTo SkipChanges: * *End If End If mcrSaveEstimates 'No changes to save SkipChanges: Range("A2") = Sheets("Misc").Range("CYMonth").Value Range("A3") = Sheets("Misc").Range("PYMonth").Value 'brings back previosly saved estimated for the new month selected RestoreEstimates Application.ScreenUpdating = True Beep End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox after change but user hits cancel to message box
because the user might change their mind and not want to save what they have
just entered. lot's a cells are impacted. "Paul Robinson" wrote: Hi Why have a cancel? regards Paul On Mar 5, 3:02 pm, VBANovice wrote: the code below is used to save some data when a user changes months on a combobox. The code prompts the user to see if he/she wants to save the estimates with a Yes, No, Cancel. yes and no cases work as expected but when the user clicks cancel, the code exits out as expected but the combox box value is now set to the new month the user selected. I would like it to be set back to the month they had in the combox before the changed it. how can I do modify the code to do that or is there a better way to do this? thanks Private Sub cboMonth_Change() Dim myCheck As Integer Dim ms As String Dim dc As Long On Error Resume Next Application.ScreenUpdating = False ms = Format(Range("cyMonthSave"), "mmm") With Sheets("Estimates") 'finds appropriate column dc = .Rows(1).Find(What:=ms, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column End With If Sheets("Merchandise Store Plan").Range("m15").Value = Sheets("Estimates").Cells(60, dc).Value Then GoTo SkipChanges: End If myCheck = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Save") If myCheck = vbCancel Then Exit Sub Else If myCheck = vbNo Then GoTo SkipChanges: End If End If mcrSaveEstimates 'No changes to save SkipChanges: Range("A2") = Sheets("Misc").Range("CYMonth").Value Range("A3") = Sheets("Misc").Range("PYMonth").Value 'brings back previosly saved estimated for the new month selected RestoreEstimates Application.ScreenUpdating = True Beep End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox after change but user hits cancel to message box
Instead of tying this to the _Change event, why not have a "ok" button that does
the work. The user changes the month and hits ok. You do some validity checks and do the work if everything is ok. VBANovice wrote: the code below is used to save some data when a user changes months on a combobox. The code prompts the user to see if he/she wants to save the estimates with a Yes, No, Cancel. yes and no cases work as expected but when the user clicks cancel, the code exits out as expected but the combox box value is now set to the new month the user selected. I would like it to be set back to the month they had in the combox before the changed it. how can I do modify the code to do that or is there a better way to do this? thanks Private Sub cboMonth_Change() Dim myCheck As Integer Dim ms As String Dim dc As Long On Error Resume Next Application.ScreenUpdating = False ms = Format(Range("cyMonthSave"), "mmm") With Sheets("Estimates") 'finds appropriate column dc = .Rows(1).Find(What:=ms, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column End With If Sheets("Merchandise Store Plan").Range("m15").Value = Sheets("Estimates").Cells(60, dc).Value Then GoTo SkipChanges: End If myCheck = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Save") If myCheck = vbCancel Then Exit Sub Else If myCheck = vbNo Then GoTo SkipChanges: End If End If mcrSaveEstimates 'No changes to save SkipChanges: Range("A2") = Sheets("Misc").Range("CYMonth").Value Range("A3") = Sheets("Misc").Range("PYMonth").Value 'brings back previosly saved estimated for the new month selected RestoreEstimates Application.ScreenUpdating = True Beep End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox after change but user hits cancel to message box
Hi
I can't see the combobox_change making any changes to cell values. Are you making changes before you have checked whether the changes should be made? If you are, they need to be made after the message box check. regards Paul On Mar 5, 4:33*pm, VBANovice wrote: because the user might change their mind and not want to save what they have just entered. *lot's a cells are impacted. * "Paul Robinson" wrote: Hi Why have a cancel? regards Paul On Mar 5, 3:02 pm, VBANovice wrote: the code below is used to save some data when a user changes months on a combobox. *The code prompts the user to see if he/she wants to save the estimates with a Yes, No, Cancel. yes and no cases work as expected but when the user clicks cancel, the code exits out as expected but the combox box value is now set to the new month the user selected. *I would like it to be set back to the month they had in the combox before the changed it. * how can I do modify the code to do that or is there a better way to do this? thanks Private Sub cboMonth_Change() Dim myCheck As Integer Dim ms As String Dim dc As Long On Error Resume Next Application.ScreenUpdating = False ms = Format(Range("cyMonthSave"), "mmm") With Sheets("Estimates") 'finds appropriate column dc = .Rows(1).Find(What:=ms, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column End With If Sheets("Merchandise Store Plan").Range("m15").Value = Sheets("Estimates").Cells(60, dc).Value Then GoTo SkipChanges: End If myCheck = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Save") If myCheck = vbCancel Then * * Exit Sub * *Else * *If myCheck = vbNo Then * * *GoTo SkipChanges: * *End If End If mcrSaveEstimates 'No changes to save SkipChanges: Range("A2") = Sheets("Misc").Range("CYMonth").Value Range("A3") = Sheets("Misc").Range("PYMonth").Value 'brings back previosly saved estimated for the new month selected RestoreEstimates Application.ScreenUpdating = True Beep End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox after change but user hits cancel to message box
If it was just Yes/No, wouldn't they still be able to close the window with
the X on the top right? "Paul Robinson" wrote: Hi Why have a cancel? regards Paul On Mar 5, 3:02 pm, VBANovice wrote: the code below is used to save some data when a user changes months on a combobox. The code prompts the user to see if he/she wants to save the estimates with a Yes, No, Cancel. yes and no cases work as expected but when the user clicks cancel, the code exits out as expected but the combox box value is now set to the new month the user selected. I would like it to be set back to the month they had in the combox before the changed it. how can I do modify the code to do that or is there a better way to do this? thanks Private Sub cboMonth_Change() Dim myCheck As Integer Dim ms As String Dim dc As Long On Error Resume Next Application.ScreenUpdating = False ms = Format(Range("cyMonthSave"), "mmm") With Sheets("Estimates") 'finds appropriate column dc = .Rows(1).Find(What:=ms, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column End With If Sheets("Merchandise Store Plan").Range("m15").Value = Sheets("Estimates").Cells(60, dc).Value Then GoTo SkipChanges: End If myCheck = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Save") If myCheck = vbCancel Then Exit Sub Else If myCheck = vbNo Then GoTo SkipChanges: End If End If mcrSaveEstimates 'No changes to save SkipChanges: Range("A2") = Sheets("Misc").Range("CYMonth").Value Range("A3") = Sheets("Misc").Range("PYMonth").Value 'brings back previosly saved estimated for the new month selected RestoreEstimates Application.ScreenUpdating = True Beep End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User hits Cancel | Excel Programming | |||
Relacing default Excel message box when user attempts to change locked cell on protected sheet. | Excel Programming | |||
Cancel Macro is user selects 'cancel' at save menu | Excel Programming | |||
Cancel Message | Excel Discussion (Misc queries) | |||
Dialog Box - User hits cancel | Excel Programming |