Hi everyone,
This is a question I am even unsure to ask as I may ask it wrong.
I am currently working on an excel workbook that contains 2 tabs of data, there are 2 more tabs but are irrelevant to this problem.
The first tab has "last Years" data in it, the 2nd tab contains new data written to it by UserForm1.
Userform1 is made up of textboxes and comboboxes(When adding new data)
I have used: With rngTarget.Cells(1, 1).EntireRow to assign various values from within the tab to public variables.
for example: varSurfaceLocation = CStr(.Cells(1, 17)) - Column 17 being the one with the data in it for this particular variable.
When referencing the sub I used ActiveCell to select the row.
The Userform textboxes are then populated with the values from the variables.
UserForm1.TextBox1.Value = varSurfaceLocation
This is all done within the module.
Then I have a private sub named UserForm_Initialize() which assigns the value from the above variable to another private variable
Location = varSurfaceLocation
Once a checkbox has be checked the user is able to edit the textbox and change the value.
In another private sub named TextBox1_AfterUpdate() the TextBox1.Value (User just adjusted) is written into the public variable
varSurfaceLocation = Me.TextBox1.Value
The public variable and the new value are compared and the backcolor of the textbox changes to yellow if they are different (Which they usually are as the new one is an adjustment), if the user was to change the value back to be the same as the original entry then the backcolor of the textbox will go back to being white.
This all works perfectly the first time it is run. However, when the user clicks another cell in the spreadsheet the textboxes are all re-populated with the new data which works fine. The problem is when the user again changes a value in the textbox as it seems that the comparison variable still has the old data from the previous selection in it.
Please help me.
I hope it's not too confusing to read but I will answer any follow up questions.
Or I could upload my workbook, it is 755kb in size and obviously has macro's attched to it.
Andy
Bookmarks