PDA

View Full Version : Learning VBA - is this a good starter project?



Mediocre Egg
10-04-2007, 08:23 AM
I'm just starting to work on learning VBA and I have finally found what seems to be a good project to use as a learning tool.

I work for a newspaper and occasionally I'll work as a liason between our internal archives researcher and our customer service department when they get a call from a reader in need of research. Currently the customer service representative gets the reader's information and plugs it into a simple Excel spreadsheet I created with text fields, checkboxes, dropdowns, and so on.

I would like to create a better form using VBA. Here is a mockup of what the form would most likely look like:

img261.imageshack.us/img261/7105/researchreqmockupbl0.gif
(these forums won't let me include a link until 5 posts are made so please copy and paste)

The end goal of the form:

Once the CSR fills out the form, they press Submit and everything is dumped into an Excel form and emailed to me. I'm not sure if this is possible.

Details of the form:

Most of the fields will be text boxes. Some things like Date, I hope to be able to auto-populate based on the current date. Other fields, like Source, State, and Country, and Time Limit will be dropdown boxes containing options.

The large text box for the actual request will not be enabled until the 2 pricing related checkboxes are marked.


So, my questions:

Does this seem like an approachable goal for a newbie or am I getting in over my head?

Will it be possible to have the form email me information when the Submit button is clicked?

Thanks!

Bob Phillips
10-04-2007, 08:49 AM
This is a very good starter project, and is eminently doable.

A word of advice.

Always use Option Explicit in your code.

Devise a set of standards for variable names and stick to it.

Start of with just a few controls, write the code to dump them to the worksheet, and the code to email it. Get that working then just add the rest bit by bit, most of it will be repetitive code so testing with a subset is good practice.

Mediocre Egg
10-05-2007, 11:36 AM
Thanks for the encouragement and advice. I have my form built up and some of the buttons coded, and the code is option explicit. I will continue plodding along.

What kind of format can I expect when my users submit this form via email? Will it be just a rough spreadsheet with the data dumped into it or can I make it so it's actually useful to me as well?

lucas
10-05-2007, 11:41 AM
Not useful as an excel file....you're starting to worry me.:devil2:

Mediocre Egg
10-05-2007, 11:50 AM
Not useful as an excel file....you're starting to worry me.:devil2:
Do you mean it's not likely to be very useful for me?

If, so - that's okay really. This is more of an excuse for me to learn how to do this.

A question - I'm working on my txtDate field, which is a text box which I hope to auto-populate with the current date.

I am using the following for that box:

Private Sub txtDate_Change()
Dim LDate As String
LDate = Date
txtDate.Value = LDate
End Sub
It does enter the current date. However, it only does it when my cursor is in the field and I press any key on the keyboard.

What am I missing there?

lucas
10-05-2007, 11:58 AM
No Egg, I was just being sarcastic....Excel is for me the most useful format to have my data returned to...especially if I'm working in Excel.

As for the question, you need to put it in the userform initialize statement so that it will be populated when the form loads:
Private Sub UserForm_Initialize()
Dim LDate As String
LDate = Date
txtDate.Value = LDate
End Sub

Mediocre Egg
10-05-2007, 12:20 PM
Awesome - that did the trick. Thanks!

I'm sure it won't be my last question :)