make textbox visible on condition of combobox selection

**zebra** · 09-17-2009, 09:55 AM

Hi,
I have a userform where the user selects a material code and a time. Based on a lookup table a formula then reads the related cost to the code and multiplies it by the time giving a total cost. However on occassion the cost needs to be entered manually so if the material code is set to "117" I need a textbox that the user will enter the correct price into. I thought I'd solved this by making a popup box which had a textbox where the manual cost was entered. This figure was then saved in the 117 cost column of the lookup. This worked except the next time the user manually enters a figure then it changes all the previous 117 entries to be the same value.

The two ways I can think of are either to use an IF argument and say IF the material code is 117 then the forumla is overriden and the cost is entered directly into the database instead of going through the lookup table.
The other is to make a textbox that's only visible if 117 is selected and then write code that will recognize if it's 117 then the lookup formula is overrided and the manually entered cost is saved straight into the cost column.

The third way I can think of is to re-write the whole workbook but this would be highly stressful! so first I thought I'd check if anyone has any suggestions.

I've attached a copy of the workbook because i'm not quite sure which are the relevent bits of code.

Thank you all so so much!

**zebra** · 09-17-2009, 10:48 AM

Ok, I've had some more thought, what if I add a textbox and set it to appear only if 117 is selected, would the code be like:

IF cmbMC.Value "117" Then txtManCost visable
IF NOT cmbMC.Value "117" Then txtManCost not visible

cmbMC is the dropdown list of material codes, txtManCost would be the textbox where the user would manually enter the price (this box is made on the version I attached originaly)

Then I would need to change the line of code that saves the userform to the database. For this cell it's currently:

.Offset(RowCount, 6).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[10],BACKGROUND!C[3]:C[6],4,FALSE)=TRUE),"""",VLOOKUP(RC[10],BACKGROUND!C[3]:C[6],4,FALSE))"

But I'd somehow need to put:

IF cmbMC.Value "117" Then Me.txtManCost.Value
IF NOT cmbMC.Value "117" Then FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[10],BACKGROUND!C[3]:C[6],4,FALSE)=TRUE),"""",VLOOKUP(RC[10],BACKGROUND!C[3]:C[6],4,FALSE))"

I know the code I've just made up won't work- I'm really bad at vb! but I'm trying and am I thinking along the right lines?