Text Box Control Source

R

Richard

I cannot stop my userform text box overwriting my control
source cell. The value for this cell is calculated
elsewhere, so i just want to display it on the userform.
How can i achieve this?

Thanks in advance

Richard
 
M

Mike Fogleman

Perhaps a sample of your userform code would help. It appears, from your
earlier post, that you want to input a value in one textbox, do a lookup on
that value, and display the results in a second textbox. Are both textboxes
on the same userform? Do both textboxes have the same controlsource? Give
specifics.
Mike
 
G

Guest

Not much code on the form to be honest, as i have to hand
it over to a non Excel person afterwards.
Both drop down boxes are on the same form. The full
scenario is:
Choose Consultant from first drop down
This is sent to spreadsheet
Choose Component from second drop down
This is sent to another cell on spreadsheet

Using lookup tables on spreadsheet i then calculate two
new values in two new cells.

It is these cells i wish to have displayed on the userform.
I have added a commandbutton to update the userform once
the dropdowns have been selected.

Hope this info is of more help

Richard
 
R

Richard

Not much code to show as file going to novice users to
amend.
The full scenario:
-One userform
-two drop down boxes:
-first chooses consultant
-second chooses component
-both values sent to separate cells on spreadsheet
-values used via lookup table to return two new values
-these valuse are to be displayed on same userform

Its the last stage i cannot achieve - i have a command
button to update the values, but i have the probelm of the
calculated values from the lookup tables (the formulas)
being overwritten.

Any help appreciated.

Richard
 
T

Tom Ogilvy

Don't use the control source property to link to a cell with a formula. Use
code to update the control on the userform. Clear the control source
property. Perhaps the click event of one of the dropdowns is the
appropriate place to trigger the update code. Or if you have established a
button to do the update, then use the click event for that.
 
R

Richard

I have inserted the folllowing code in the combo click
event as you suggested, but it only seems to work once
when the form is opened.

TextBox1.Value = Range("H30")

Am i missing something to reset it so i can change it as
often as i like?

Thanks for your help

Richard
 
T

Tom Ogilvy

Each time the click event of the combobox is fired then Textbox1 should be
updated.

You might want to qualify the range with the worksheet name

Private Sub Combobox1_Click()
TextBox1.Value = Worksheets("Sheet3").Range("H30")
End Sub
 
Top