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

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

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

.

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

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

.

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

.