Solved: Userform + Range=#value

[jmaocubo]

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]

does B3 on sheet 4 have a formula? If so, what is it?

[jmaocubo]

does B3 on sheet 4 have a formula? If so, what is it?
B3 as no formula

[lucas]

Then cell A5 of sheet 2 must have a formula, right? If so, what is it?

[jmaocubo]

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]

I get an error with your formula

[jmaocubo]

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]

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,"")

[SamT]

=IF('sheet4'!B3="","#Value",{your formula here})