Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Userform Textbox re-writing cells in Excel
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Userform Textbox re-writing cells in Excel
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Pete" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Userform Textbox re-writing cells in Excel
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" wrote: 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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Pete" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Userform Textbox re-writing cells in Excel
"Pete" wrote in message ... 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.. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Userform Textbox re-writing cells in Excel
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 "Dick Kusleika" wrote: "Pete" wrote in message ... 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.. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Userform Textbox re-writing cells in Excel
Good luck, Pete. You've already found the best Excel resource, these
groups, so be sure to post a question if you get stuck. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Pete" wrote in message ... 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 "Dick Kusleika" wrote: "Pete" wrote in message ... 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.. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i format a textbox on a userform created in excel | New Users to Excel | |||
Excel VBA - Userform Textbox Manipulation | Excel Programming | |||
Excel VBA - Userform textbox formatting | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming | |||
Userform Textbox writing to a cell | Excel Programming |