Just recently I banged out a quick little example for a reader who wanted to know how to quickly and easily store values from a userform created using VBA, and then prepopulate the userform the next time it's brought up with those stored values. Basically, I created a new worksheet in the file that would remain hidden and had a named ranged in that worksheet for each field in the userform. Then, when the userform was called up, I grabbed the values from those named ranges and put them in the inputs in the form. Then, when the userform was submitted, I changed the values in those named ranges to those currently in the userform. Pretty slick.
Like so many things Excel+VBA, it's easy when you know how. I thought my Excel_Geek Insiders would appreciate this simple example.
Later,
Excel_Geek
(how about that, three posts in one day after two months with nothing...can you tell I've been busy?)
Wednesday, February 27, 2008
Prepopulating a Userform
Subscribe to:
Post Comments (Atom)
2 comments:
I've been using that technique since Excel 95! :)
Then I found that when I'm testing out the app, the user gets my test values, unless I remember to clear out the form.
I decided to save the Form Values in the registry with the VBA commands:
SaveSetting()
GetSetting()
This way I can do all my testing and the user will still have their own default values that they use. It's much more convenient as several users can use the same macro and have their own settings. I even have a form for setting folders for report saving or for reading reports. Again each user has there own set of reports with the same macro.
Thanks for the tips, Nick.
Post a Comment