Best way to plan out my user form

[CindyHa]

I work with a spreadsheet frequently at work that has about 25 columns across; each row contains some data about a person, such as last name, first name, etc.

A few of us share the worksheet, and it's used during meetings, viewed on a big screen.

I hate having to scroll around to view and enter data (it's hard on my old eyes!), so I want to create a form that sits off to the side (non-modal). Besides being able to view the data, I want to be able to edit existing data, and create a new employee row.

I created a simple play worksheet with 4 columns, and successfully built a form, and wrote code that lets me scroll from the first row to the last row, populating my fields with the data from the spreadsheet. And, while simple to most of you probably, I was pretty proud that I could also click anywhere in the spreadsheet and have that row's data displayed in my form!

So far, so good.

But, as I get to the point of editing the data and having the edits written back to the spreadsheet, I'm starting to wonder about my high level logic.

1. Because I want the data to be "read only" in my form, unless someone purposefully clicks an "Edit Button", I set the Locked property of each field to True.
It works like I hoped, I can move Next/Previous and see all my records, and can not accidentally edit a field.

2. Next, I added an "Edit' button. This is where I start to get a little worried that my approach may be wrong.
Seems like at this point I have to programmatically set the Locked property to "False" for each control on my form?

3. Just to move forward, I did that with my 4 column test spreadsheet...but now I'm stumped on how to determine if a value was changed so I can write the new value back to the spreadsheet. I've Googled, Binged, and YouTubed, but haven't found any sample code or logic to nudge me along.
I suppose any time the Edit button is clicked, and then the Save button, I could re-write ALL the fields to the spreadsheet? I'm not too terribly worried about waste or inefficiency because our spreadsheet is small. But, I'd like my code to be good enough so that I can build on it at a later date if I want.

Or, do I figure out how to make global variables for each field, and when Save is clicked check to see if the "before" value matches the "current" value, and if so, write the value to the spreadsheet?

3. Finally, we do filter our rows, when employees reach the "completed" stage they are filtered out). I suspect that's going to trip me up later on, but I haven't had to worry about that yet....

Thanks for any help/advice!

[noboffinme]

I think your form belongs in MS Access as it has more capability.

[CindyHa]

Unfortunately, nobody has a copy of Microsoft Access, and it's not in the budget.

[viggen66]

Look at this, Excel has its own Form Entry


http://www.addictivetips.com/microso...a-input-forms/

[CindyHa]

Thanks. I did take advantage of that, but with 30 or so columns I end up with one big long skinny form, and it's only marginally easier to view/use than the spreadsheet itself.

[noboffinme]

Are you sure that you don't have MS Access?

Sometimes you need to go to Control Panel >> Programs >> MS Office >> Change or Remove Programs >> check the checkbox for MS Access.

I'm using MS Office 2013 but might have a different version.

The more I look at your requirements, the more I think you need a relational database app like Access.

[CindyHa]

Yes, I'm sure. :-)

I'm going to play with declaring each text box as a global variable and if the user clicks away from a given record, I'll check to see if the current value of the fields matches what was in the global variables. I know from the little bit of coding I've done that it's best to use global variables sparingly, but it's all I can think of right now.