|
store variables
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 |
store variables
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" wrote: 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 |
store variables
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" wrote in message ... 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" wrote: 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 |
store variables
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. "Patrick C. Simonds" wrote: 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" wrote in message ... 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" wrote: 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 -- Dave Peterson |
store variables
Copy and paste error!
This line: vacholiday as long = rng(1,5).value should have been: vacholiday = rng(1,5).value Dave Peterson wrote: 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. "Patrick C. Simonds" wrote: 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" wrote in message ... 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" wrote: 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 -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 12:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com