using vba to update vba form

  • Thread starter Thread starter jrh
  • Start date Start date
J

jrh

I created a form that uses radio buttons to populate class
rosters on a spreadsheet. Before, I would need to type in
the kid's name, the class they were taking, what day the
class was, who the teacher was, and how much the class
cost. Now, I just type in the kid's name and hit a radio
button for the class name. Hitting that class name
triggers the day, teacher, and cost to be populated on the
spreadsheet automatically. This saves time when actually
populating the spreadsheet, but when I have to update the
form with all new classes each time and then change the
code to populate certain things for that new class, this
ends up taking more time than doing it manually. Is there
a way I can write code that gives me another form where I
type in a class name with the corresponding teacher, day,
and cost once and this automatically upates the code that
popluates these things? Or is there another more
straightfoward way to do this that I don't see?

Here is an example of the code that is triggered when a
certain radio button (class name) is picked, in this case
Math.

'Transfer the Class
If OpMath Then Cells(NextRow, 5) = "Math"

'Transfer the Teacher
If OpMath Then Cells(NextRow, 6) = "Mrs. Smith"

'Transfer the Cost
If OpMath Then Cells(NextRow, 7) = 30

'Clear the controls for the next entry
OpMath = False

Clearing the radio buttons on the form, setting up new
radio buttons on the form, and then writing this code
takes too long. Can I just input the new data items in
boxes and have the vba do it all itself?

Thanks.
 
jrh,

It's easy enough, but we need the code for populating the form, etc.

If you would like, you could send me the workbook, and I will add it for
you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
 
Hi JRH,

Well if I followed your post correctly, it sounds like you
need a little database. You might try creating a worksheet
that is like a "Table", storing the ClassName, Teacher,
Day, Cost, etc. You could then change your option buttons
to a list box (based off the Table data) to make your
selections/updates. New classes (or updating existing
classes) could be manually entered into the "Table"
worksheet, or you could create a little data entry form.

If you want to send me an example of your existing
workbook, I could take a look and perhaps throw something
together for you pretty quick.

Hope that gave you some ideas, there are probably a
handful of solutions that would work.

Regards,
James S
 
jrh,

One way you could this is the add a worksheet that
contains a row for each class name, with the related
information in columns on the same row. Then make the
Caption of each option button match the class names. You
then write code (remove all your existing code) to look
up the matching data and copy it to your existing sheet.

If you set it up this way, changing the course related
data (which must happen every semester) would only
require updating the new sheet once. Adding a new class
would only require entering the data for the course once
and adding a new option button with a caption to match.

I'm sure others will post other ways of doing this.
I look forward to learning new methods to approach this
common task.

Vickie
 
Back
Top