Simplifying data entry with a list box and VBA code

re: Simplifying data entry with a list box and VBA code

Pbowser — Thu, 22 Mar 2012 14:37:47 GMT

Question: I have a file that has over 300 lines of data. does anyone know a code that will add in two lines after each data. I am new at VBA so any help will be helpful.

thanks

re: Simplifying data entry with a list box and VBA code

ShelLuser — Mon, 20 Feb 2012 05:03:37 GMT

Thanks for the update!

Its what I really came to enjoy and respect about this blog; you guys don't merely share interesting stories but you keep track of comments and such too.

And I really enjoyed following up on your article. Thanks for sharing, it maybe a small sub but it does make a difference.

re: Simplifying data entry with a list box and VBA code

Gary Willoughby — Wed, 15 Feb 2012 23:56:26 GMT

Thanks for your comments; I just added the code for the ClearBoxSelections subroutine -- good catch. I'd also like to mention that another reason we needed to use VBA was to get the numeric IDs (1574, 1576, etc., as shown in the last image) written correctly in the adjacent cell. Having users do that manually would have likely introduced errors into the worksheet. Anyway, glad you found this interesting.

re: Simplifying data entry with a list box and VBA code

ShelLuser — Wed, 15 Feb 2012 22:31:20 GMT

Thanks for an interesting article!

I have, however, a few comments.. Your example is a bit hard to follow here and there, especially the part where you first present us with all the code and then start to repeat snippets of if to explain. I see what you're trying to do, but it makes it a bit more difficult to follow. Especially since you talk about 2 Subs while in fact you only shared 1 (either I'm overlooking it, but it seems to me as if "ClearBoxSelections" is no where to be found).

The confusion should be obvious; even without the sub to clear the checkboxes this behavior will occur anyway at times ;-) Still, no offense but I get the feeling you're making it a little bit too hard to follow for the average user.

One small bit of (positive!) criticism.. I think you're better of when keeping explicit declarations turned on (iow; the code always starts with the line "Option Explicit" to set this global option). It helps you to keep your code "managable" and often makes it easier to read because all variables in your subs will need to be declared first.

Still, I applaud this approach! I've written in before, I'll do it again: In my opinion people can really benefit from playing with VBA. Its often not as hard as you may think it to be, while it actually gives you tremendous freedom to make the software do what /you/ want it to do.