VBA Userform Textbox re-writing cells in Excel

[Guest]

I'm new to using VBA with Excel--I have Office 2003. I'm building a form in
VBA that will have random multiple-choice questions inserted into it from a
VLookup function's results in the accompanying spreadsheet. There is a big
table with the questions in column C and the 4 answer choices in columns d to
g, and the vlookups are on the top in cells C3 to G3.

I tried naming the cells that have the Q&A VLookup formulas as named ranges
in the spreadsheet, and then referencing the ranges in to Control Source of
the Properties box for each Q&A caption on the form. It works once but then
values get written in place of the VLookup formulas. Same thing happened
when I inserted the cell's names in ControlSource Property boxes--the cells
in the spreadsheet get the vlookup clobbered by the value of the first
result....and the vlookup formulae ares no longer there.

I tried changing ENABLED from true to false in the Properties box, and also
changed LOCKED from False to True...still I get the same problem.

I know I may be doing this in a not-very-elegant way--it does not need to be
a "hardened" program, just a prototype for a concept.

Thanks!

 

[Dick Kusleika]

Pete

What kind of controls are you using: lables, textboxes, comboboxes,
checkboxes? How are the users selecting the right answer? Is there any
code behind the form?

Generally, if you use ControlSource, and changes you make to the control
will be written back to the cell and any changes you make to the cell will
be written to the control. I can't tell from your description where the
controls are being changed that would cause that.

 

[Guest]

I'm using textboxes within a UserForm and have not written any code for that
part yet. It also has action buttons # 1 to 4 for the test. I'll be the
only user. So I guess I need to write some code rather than just work with
the textbox properties? I did not realize the text boxes would write back to
the spreadsheet itself.

Thanks for your post and any additional suggestions you can provide!

Regards,
Pete

 

[Dick Kusleika]

I'm using textboxes within a UserForm and have not written any code for that
part yet. It also has action buttons # 1 to 4 for the test. I'll be the
only user. So I guess I need to write some code rather than just work with
the textbox properties?

That's always my advice. I hate those properties. And if you don't use
them, you could use a label instead of a textbox, although you may want a
textbox for appearance sake. Name those ranges and your Userform_Initialize
event will look like

Me.tbxFirst.Text = Sheet1.Range("Choice1").Value
Me.tbxSecond.Text = Sheet1.Range("Choice2").Value
etc..

 

[Guest]

Thanks! I got it to work with your advice after an undue amount of poking
around...due to my only having studied VB and VBA a few hours so far.

I am realizing that with all the elements I have to string together to get
what I am building to work, I need to study the principals some more rather
than struggle bit by bit with each little component. Am going back to some
introductory manuals and e-learning tools to start again from the ground up.

Wish me luck!

Regards,
Peter