XL: Conditional Data Copy in VBA 1

[MeGustaXL]

Hi All

I need a kind of ‘Conditional Save and Copy’ routine for my project. It’s hard to explain briefly without attaching reams of code or the workbook itself, but I’ll try.

Pseudo code working so far:

Pseudo Sub DataEntry()
Show a Form
User selects/types value in ComboBox and rest of form controls
OK button clicked
If ComboBox .Value doesn’t exist in Sheet1, Col A then 
   Add a new line with all the data
   Copy and add the new line to Sheet2
   Sort Sheet1 by Col A
   Clear the Form and start again
Else
   Highlight matching row on Sheet1 and load form with row values
   User reads/edits data in form
OK button clicked
Update highlighted row with data from form
Copy and add this line to Sheet2 '<= Do this conditionally
Clear the Form and start again
End Sub

What I need is for the “Copy and add this line to Sheet2” line 2nd from end to only happen if 2 or 3 critical Text Boxes have been changed
These critical boxes contain time and date values, which are cumulatively summed on Sheet2. So I don’t want to add another lot of times, if the User only looked at the existing record without changing any of the critical values

I’m working on (a) setting a “CopyRecord” flag if the time value boxes are changed, or (b) Stepping through the highlighted row, and setting the flag if the Form data is different from Sheet1.

Which (if either!) of these is the quickest/slickest = Best way?
Or is there a super-simple method I’m missing?

Thanks in advance for any and all hints or guidance.



Chris

Varium et mutabile semper Excel

 

[Nelviticus]

Couldn't you just have a global boolean variable called 'Changed' in your form code that's initialised to 'False', and gets set to 'True' in the 'Change' event of your critical text boxes? You could test it and only execute your copy line if it was True.

If the user edited the value but changed it back again you'd still copy the line unnecessarily, but I'm sure that wouldn't be too horrendous!

N.

 

[MeGustaXL]

See! I just knew there was an instant way of doing it!

Thanks a bunch 'N' - have a STAR, and if you're a girl, have a big kiss as well!

I just declared 'Dim Changed As Boolean' in the General module then set it to False when the Form is Initialised, and again if a match is found and the data's loaded in.
Then set it True in the Change() event of the necessary boxes, et Voila! (as Skip would say )

Sheer Genius!

Chris

Varium et mutabile semper Excel