PDA

View Full Version : [SOLVED] Form value in cell not included in formula



lucas
09-27-2004, 11:23 AM
I have a userform that imputs to a cell. In turn the cell value is used in a formula. my problem is that with my form set up the way it is, the value is not included in the formula for some (probably basic) reason. I have tried calculate, format and several other things but I am missing something..

I wish someone would take a look at it for me and see what I am doing wrong.
Thanks

Zack Barresse
09-27-2004, 11:46 AM
Hi,

The numbers in your cells are formatted as text. ;)

lucas
09-27-2004, 11:49 AM
Zack,

I tried that, it didn't change anything?

TonyJollans
09-27-2004, 12:11 PM
You need to put a number in the cell. At the moment you are puting text there.

Change to:



Private Sub TextBox2_Change()
Sheet1.Range("J4") = CInt(TextBox2)
End Sub

(or use CLng or CDbl or whatever is appropriate). After that you'll be able to use numeric formatting on the cell to show the number as you wish and it will be included in the SUM

Zack Barresse
09-27-2004, 12:16 PM
What did you try? I converted them to numbers and the SUM seemed to work for me.. Right off hand a conversion would work for you ..


=SUM(--J4,--R4,--Z4,--AH4,--AP4,--AX4,--BF4,--BN4,--BV4,--CD4,--CL4,--CT4,--DB4,--DJ4,--DR4,--DZ4,--EH4,--EP4,--EX4,--FF4,--FN4,--FV4,--GD4,--GL4,--GT4,--HB4,--HJ4,--HR4,--HZ4,--IH4)

I don't have much time now, but a better formula could also work, you've reached your SUM reference amount limit.

mdmackillop
09-27-2004, 12:29 PM
Hi Tony,
I alway thought there should be a function for that, but I never came across it. My workaround was to multiply by 1, which should also work here.
MD


Sheet1.Range("J4") = (TextBox2) *1

Zack Barresse
09-27-2004, 01:25 PM
Ah, my mistake. I thought you were looking for a formula workaround. Nice Tony. :)

MD: That's pretty good, never thought of doing that in vba, I always used the INT worksheetFunction in vba. Btw, when are you getting a *front* pic up? ;)

lucas
09-27-2004, 06:16 PM
Thanks Tony,

That works like a charm.
Thanks for looking at it for me Zack, I realize there are some basic things wrong with it (someone else gave it to me to work on) but I got stumped on the number input. I just duplicated the problem from a large workbook.