View Full Version : Solved: Userform + Range=#value
jmaocubo
04-01-2010, 10:12 AM
Hello
Can you help me?
I have a userform with a textbox linked to cell A5 of sheet2, but until I complete the cell B3 of the sheet 4 it's shown the value "#VALUE" (error value number divide by 0).
So it gives error in textbox.
I'm using the following code:
Private Sub UserForm_Click()
If Intersect(Worksheets("sheet4").Range("B3")) Is Nothing Then
Exit Sub
Else
Me.TextBox1.Value = Format(Worksheets("sheet2").Range("A5").Value, "0.00 €")
End If
End Sub
Thanks in advance
Miguel
lucas
04-01-2010, 10:47 AM
does B3 on sheet 4 have a formula? If so, what is it?
jmaocubo
04-01-2010, 10:57 AM
does B3 on sheet 4 have a formula? If so, what is it?
B3 as no formula
lucas
04-01-2010, 11:01 AM
Then cell A5 of sheet 2 must have a formula, right? If so, what is it?
jmaocubo
04-01-2010, 11:06 AM
Yes.
=(D68+D80)/('sheet4'!B3*sheet7'!G123*sheet1'!A34*A5)
The fact that the value is "# Value" is normal and is intended.
Workers fill up and then the admin is that it places the value in cell B4
lucas
04-01-2010, 11:15 AM
I get an error with your formula
jmaocubo
04-01-2010, 11:31 AM
Hi Lucas
first of all thank you
I used a formula as an example and then with a general code I would adapt to my userform.
The formula I have is too long, using more than a workbook.
what need is to ensure that when there is no valid value in the cell then has nothing in the textbox.
Sorry I can't explain myself very well (My English is not so good)
lucas
04-01-2010, 11:36 AM
what need is to ensure that when there is no valid value in the cell then has nothing in the textbox.
I think I understand but to do that we will need to see the formula. I can give you an idea of how but you would need to work it out for yourself.
Look up iserror in help for one thing.
depending on the forumula you might also us an if statement to check the cell that is throwing the error. Something along the lines of:
=IF(H5<>"",H5-G5,"")
=IF('sheet4'!B3="","#Value",{your formula here})
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.