VBA excel populate textbox from range

[Cozzy123]

Hi,

I would really appreciate some help. I am trying to auto populate TB2 either A/B/C/NA with the value of TB1.

For example

If TB1 has a value less than 5150 then TB2 will populate NA
If TB1 has a value of 5150 - 5350 then TB2 will populate A
If TB1 has a value of 5351 - 5550 then TB2 will populate B
If TB1 has a value of 5551 - 5750 then TB2 will populate C
If TB1 has a value greater than 5751 the TB2 will populate NA

I have managed to do it in Excel but not in VBA.

Assistance appreciated.

Thanks

 

[ranman256]

insert a module (if you dont have any)
paste this code.


usage:
=CalcVal(cell or textbox)

Function CalcVal(ByVal pvTb1)
Select Case True
   Case pvTb1 < 5150
      CalcVal = "NA"
   Case pvTb1 >= 5150 And pvTb1 <= 5350
      CalcVal = "A"
   Case pvTb1 >= 5151 And pvTb1 <= 5550
      CalcVal = "B"
   Case pvTb1 >= 5551 And pvTb1 <= 5750
      CalcVal = "C"
   Case pvTb1 > 5751
      CalcVal = "NA"
End Select
end function

 

[Cozzy123]

Many thanks for the reply, I'm not great with VBA so apologies.

I have copy+pasted the code into TB2 but it didn't show. I'm sure I'm doing something wrong? do I need to name TB1 somewhere in the code?

Thanks for your help.