store variables

[Patrick C. Simonds]

I use the code below to put the values of the respective rng's in the
TextBoxes when the UserForm opens. What I am wondering is if there is anyway
to store those values so if the user clicks on the Cancel button the values
will be returned to there cells replacing any edits that might have taken
place.



Private Sub UserForm_Initialize()
Dim rng
Set rng = Cells(ActiveCell.Row, 1)

TextBox1.Text = rng(1, 4).Value 'ALI Time
TextBox2.Text = rng(1, 5).Value 'Vacation In Lue Of Holiday
TextBox3.Text = rng(1, 6).Value 'Floating Holiday
TextBox4.Text = rng(1, 7).Value 'Carry Over Hours
TextBox5.Text = rng(1, 8).Value 'Other Adjustments
TextBox6.Text = rng(1, 12).Value 'Comments

End Sub

 

[Dave Peterson]

You could store them in other variables, but I would only write to the worksheet
cells after the user clicked the Ok button--not after each change to any
checkbox.

 

[Patrick C. Simonds]

While I agree that it generally would be best to only write to the worksheet
OK, in this case they need to see the impact on the worksheet before they
commit to it.

Below is an example of my code for putting the current content of the cell
into the TextBox of the UserForm. What I can not figure out is how to also
turn that value into a Variable and then what to place in the code that runs
when I click on the Cancel Button to place the value back into the rng(1,4).




Private Sub UserForm_Initialize()
Dim rng
Set rng = Cells(ActiveCell.Row, 1)

TextBox1.Text = rng(1, 4).Value 'AIL Time

End Sub

 

[Dave Peterson]

dim ALITime as Variant 'string, long, what????
dim VacHoliday as Long 'what should they be?
dim FloatHol as long
dim CarryOverHours as double
dim OtherAdj as double
dim UserComments as string

Private Sub UserForm_Initialize()
Dim rng As Range
Set rng = Cells(ActiveCell.Row, 1)

alitime = rng(1,4).value
vacholiday as long = rng(1,5).value
...

TextBox1.Text = rng(1, 4).Value 'ALI Time
TextBox2.Text = rng(1, 5).Value 'Vacation In Lue Of Holiday
TextBox3.Text = rng(1, 6).Value 'Floating Holiday
TextBox4.Text = rng(1, 7).Value 'Carry Over Hours
TextBox5.Text = rng(1, 8).Value 'Other Adjustments
TextBox6.Text = rng(1, 12).Value 'Comments

End Sub

By declaring the variables outside any procedure in this module, they'll be able
to be seen by any procedure in that module.

I didn't know how to declare each of the variables, though.

 

[Dave Peterson]

Copy and paste error!

This line:
vacholiday as long = rng(1,5).value
should have been:
vacholiday = rng(1,5).value