closng userform and canceling changes

[Jacob]

I have userform that I would like to have access to on and off
throughout the usage of a spreadsheet. I want to ability to open it
and then cancel if the user decides that the changes are not necessary.
currently I have an OK and a Cancel button on the form. If the user
clicks OK, the input is written to the spreadsheet. If the user clicks
Cancel the form is hidden using UserForm1.Hide and the input,
regardless of any changes, is not written to the spreadsheet. my
problem is this: even though the input is not written to the
spreadsheet, it is saved within the userform. I do not want this. for
instance, if I open the form and checkbox1 is unchecked and I check it
then click Cancel, the form is hidden and nothing on the spreadsheet
changes. However, when I reopen the form, checkbox1 is still checked.
how can I fix this?

 

[John]

Hi Jacob,

I would split up the functionality a little cleanup code on each button
click event. So for example:

Private Sub UserForm_Initialize()
Call SetDefaultSettings
End Sub

Private Sub cmdOK_Click()
'Add values to workbook code
Call SetDefaultSettings
Me.Hide
End Sub

Private Sub cmdCancel_Click()
'Ignore add values to workbook code
Call SetDefaultSettings
Me.Hide
End Sub

Private Sub SetDefaultSettings()
Me.chkBox1.Value = False
'Plus other changes you want to make
End Sub

Hope that helps

Best regards

John

 

[Robert Bruce]

Hi Jacob,

I would split up the functionality a little cleanup code on each
button click event. So for example:

Private Sub UserForm_Initialize()
Call SetDefaultSettings
End Sub

Private Sub cmdOK_Click()
'Add values to workbook code
Call SetDefaultSettings
Me.Hide
End Sub

Private Sub cmdCancel_Click()
'Ignore add values to workbook code
Call SetDefaultSettings
Me.Hide
End Sub

Private Sub SetDefaultSettings()
Me.chkBox1.Value = False
'Plus other changes you want to make
End Sub

Or just call SetDefaultSettings from Userform_activate which will run every
time the form is unhidden.

Rob

 

[John]

Ah, didn't know that one. Much simpler!

Thanks Rob

 

[Jacob]

thanks for the help.

Ah, didn't know that one. Much simpler!

Thanks Rob