Help for Code for Cancel Button on Userform.

Hi there.

This is my first post here, but I've had a search before and I couldn't quite find exactly what I was looking for. (I'm also not very good with vba as you will probably gather shortly)

I am running a userform off a checkbox. The userform is essentially a pop out of a load of input boxes. So far so good. However, when I change a value and then hit the cancel button i've created, that value still changes the input before unloading the userform.

Is there a code I can use for the cancel button itself to ignore any changes in the input box on the user form, before unloading and changing the spreadsheet data?

An alternative I was thinking about was including an if statement for all the input boxes stating something along the lines of:

if cancel = true then
inputbox = *cell reference*
end if.

What do you think?

I have found this:

1) At the top of my module (before the Function) I placed:
Public blnCancel as Boolean

2) After DoEvents (which was in my Loop) I added the code:
If blnCancel = True Then
Exit Function
End If

3) On my form I added a Cancel button with the OnClick event:
blnCancel = True

But this doesn't seem to work either/I'm unable to correctly incorporate this code with mine.

I have also tried this code for my cancel userform button without success:

Private Sub CancelButton_Click()

If CancelButton = True Then

SaltRouteBox.Value = Cells(3, 3)
SaltSpreadBox.Value = Cells(4, 3)
SaltWidthBox.Value = Cells(5, 3)
SaltPriceBox.Value = Cells(8, 3)
SaltLengthBox.Value = Cells(9, 3)
SaltDistanceBox.Value = Cells(11, 3)
SaltVehicleBox.Value = Cells(14, 3)
SaltWagesBox.Value = Cells(15, 3)
SaltHoursBox.Value = Cells(16, 3)

Else: Unload UserForm1

End If

End Sub

The moment the cancel button is depressed, the new input is placed rather than ignoring whatever input is in the box and leaving the spreadsheet data unchanged.

yes, sorry. The 'input box' on the user form is a textbox.

here is the code:

Private Sub Apply_Click()


Cells(3, 3) = SaltRouteBox.Value
Cells(4, 3) = SaltSpreadBox.Value
Cells(5, 3) = SaltWidthBox.Value
Cells(8, 3) = SaltPriceBox.Value
Cells(9, 3) = SaltLengthBox.Value
Cells(11, 3) = SaltDistanceBox.Value
Cells(14, 3) = SaltVehicleBox.Value
Cells(15, 3) = SaltWagesBox.Value
Cells(16, 3) = SaltHoursBox.Value

If SaltRouteBox.Value = "" Then
MsgBox "Please enter a value for the Number of Routes/Vehicles."
ElseIf SaltRouteBox.Value < 0 Then
MsgBox "Please enter a positive value for the Number of Routes/Vehicles."
ElseIf WorksheetFunction.IsText(Cells(3, 3)) Then
MsgBox "Please enter a numeric value only for the Number of Routes/Vehicles."

ElseIf SaltWidthBox.Value = "" Then
MsgBox "Please enter a value for the Average Lane Width."
ElseIf SaltWidthBox.Value < 0 Then
MsgBox "Please enter a positive value for the Average Lane Width."
ElseIf WorksheetFunction.IsText(Cells(5, 3)) Then
MsgBox "Please enter a numeric value only for the Average Lane Width."

ElseIf SaltLengthBox.Value = "" Then
MsgBox "Please enter a value for the Lane Length."
ElseIf SaltLengthBox.Value < 0 Then
MsgBox "Please enter a positive value for the Lane Length."
ElseIf WorksheetFunction.IsText(Cells(9, 3)) Then
MsgBox "Please enter a numeric value only for the Lane Length."

ElseIf SaltDistanceBox.Value = "" Then
MsgBox "Please enter a value for the Total Distance per Event."
ElseIf SaltDistanceBox.Value < 0 Then
MsgBox "Please enter a positive value for the Total Distance per Event."
ElseIf WorksheetFunction.IsText(Cells(11, 3)) Then
MsgBox "Please enter a numeric value only for the Total Distance per Event."

ElseIf SaltPriceBox.Value = "" Then
MsgBox "Please enter a value for the Price of Gritsalt."
ElseIf SaltPriceBox.Value < 0 Then
MsgBox "Please enter a positive value for the Price of Gritsalt."
ElseIf WorksheetFunction.IsText(Cells(8, 3)) Then
MsgBox "Please enter a numeric value only for the Price of Gritsalt."

ElseIf SaltSpreadBox.Value = "" Then
MsgBox "Please enter a value for the Spread Rate of Gritsalt."
ElseIf SaltSpreadBox.Value < 0 Then
MsgBox "Please enter a positive value for the Spread Rate of Gritsalt."
ElseIf WorksheetFunction.IsText(Cells(4, 3)) Then
MsgBox "Please enter a numeric value only for the Spread Rate of Gritsalt."

ElseIf SaltVehicleBox.Value = "" Then
MsgBox "Please enter a value for the Vehicle Running Cost per Kilometer."
ElseIf SaltVehicleBox.Value < 0 Then
MsgBox "Please enter a positive value for the Vehicle Running Cost per Kilometer."
ElseIf WorksheetFunction.IsText(Cells(14, 3)) Then
MsgBox "Please enter a numeric value only for the Vehicle Running Cost per Kilometer."

ElseIf SaltWagesBox.Value = "" Then
MsgBox "Please enter a value for the Wage Rate."
ElseIf SaltWagesBox.Value < 0 Then
MsgBox "Please enter a positive value for the Wage Rate."
ElseIf WorksheetFunction.IsText(Cells(15, 3)) Then
MsgBox "Please enter a numeric value only for the Wage Rate."

ElseIf SaltHoursBox.Value = "" Then
MsgBox "Please enter a value for the Number of Hours to be Paid."
ElseIf SaltHoursBox.Value < 0 Then
MsgBox "Please enter a positive value for the Number of Hours to be Paid."
ElseIf WorksheetFunction.IsText(Cells(16, 3)) Then
MsgBox "Please enter a numeric value only for the Number of Hours to be Paid."


Else: Unload UserForm1

End If



End Sub

Private Sub CancelButton_Click()

If CancelButton = True Then

SaltRouteBox.Value = Cells(3, 3)
SaltSpreadBox.Value = Cells(4, 3)
SaltWidthBox.Value = Cells(5, 3)
SaltPriceBox.Value = Cells(8, 3)
SaltLengthBox.Value = Cells(9, 3)
SaltDistanceBox.Value = Cells(11, 3)
SaltVehicleBox.Value = Cells(14, 3)
SaltWagesBox.Value = Cells(15, 3)
SaltHoursBox.Value = Cells(16, 3)

Else: Unload UserForm1

End If

End Sub

Private Sub CommandButton1_Click()

Sheets("Data on Events").Range("C42:C58").Copy _
Sheets("INPUTS").Range("C3")

End Sub

Private Sub CommandButton2_Click()

Sheets("Data on Events").Range("C62:C78").Copy _
Sheets("INPUTS").Range("C3")

End Sub

Private Sub CommandButton3_Click()

Sheets("Data on Events").Range("C82:C98").Copy _
Sheets("INPUTS").Range("C3")

End Sub

Private Sub CommandButton4_Click()

Sheets("Data on Events").Range("C102:C118").Copy _
Sheets("INPUTS").Range("C3")

End Sub

Private Sub CommandButton5_Click()

Sheets("Data on Events").Range("C122:C138").Copy _
Sheets("INPUTS").Range("C3")

End Sub

This is an image of the userform, i think it will help to understand the question

userform.png

Ah, that's the original code I had for the cancel button as well, simply unload the userform. But for some reason, it changes the value of the cell with whatever value is in the text box when cancel is clicked as well.

Apologies for not using the code tags.

Yes, it seems strange. The inputs which change will be on the top left on the "input" worksheet. To load the userform, hit either of the check boxes also on the "input" worksheet.

121.xlsm

ah ok. I'm having a little trouble understanding exactly what to do.

I have cleared all the textbox's control source properties.

I don't know where to add the userform initialise procedure. I seem to be going round in circles. With the control source removed, the textbox doesn't link with the cell. So would I need to add another line of code to paste a value after the else if statements as well?

I understand what you are saying, but can't seem to put it into practise.

ahh, I did not realise it was that simple!

Thank you so much for helping me!