Two text boxes write in each the problem

M

myfe

Board Regular
Joined
Jul 1, 2012
Messages
58
Hi, </SPAN>
I have this code:</SPAN>

Private Sub TextBox135_Change()
On Error Resume Next
TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")

End Sub
Private Sub TextBox136_Change()
On Error Resume Next
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
TextBox138.Value = Format(CDbl(TextBox136.Value) - CDbl(TextBox90.Value), "0.0000")
TextBox135.Value = Format((CDbl(TextBox137.Value) - CDbl(TextBox90.Value)) * CDbl(TextBox86.Value), "0.0000")
Click to expand...



it suppose to take one input and calculate for the other variables the problem is that once it writes the outputs the other text box it activates the second text box code so I keep getting the error ‘13’ and the result is the same no matter what number I put please help me to solve this problem.</SPAN>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Thanx Andrew,


I got the concept it helped me a lot but I just need to know how to implement it </SPAN>

Thanx
 
Upvote 0
Hi,
I did this (all inside the user form) but it gave me the error
Compile error method or Data member not found and

It highlights :
</SPAN>
Private Sub UserForm_Initialize() (in yellow)</SPAN>
&
EnableEvents (in Blue)</SPAN>


End Sub

Public EnableEvents As Boolean

Private Sub UserForm_Initialize()
Me.EnableEvents = True
End Sub

Private Sub TextBox135_Change()

Me.EnableEvents = False

On Error Resume Next
TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")

Me.EnableEvents = True
End Sub

Private Sub TextBox136_Change()

Me.EnableEvents = False

On Error Resume Next
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
TextBox138.Value = Format(CDbl(TextBox136.Value) - CDbl(TextBox90.Value), "0.0000")
TextBox135.Value = Format((CDbl(TextBox137.Value) - CDbl(TextBox90.Value)) * CDbl(TextBox86.Value), "0.0000")

Me.EnableEvents = True
Click to expand...

what should I change
 
Last edited:
Upvote 0
Try:

Rich (BB code): 
Public EnableEvents As Boolean

Private Sub UserForm_Initialize()
    Me.EnableEvents = True
End Sub

Private Sub TextBox135_Change()
    If Me.EnableEvents = False Then Exit Sub
    Me.EnableEvents = False
    On Error Resume Next
    TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
    TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
    TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
    TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
    Me.EnableEvents = True
End Sub

Private Sub TextBox136_Change()
    If Me.EnableEvents = False Then Exit Sub
    Me.EnableEvents = False
    On Error Resume Next
    TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
    TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
    TextBox138.Value = Format(CDbl(TextBox136.Value) - CDbl(TextBox90.Value), "0.0000")
    TextBox135.Value = Format((CDbl(TextBox137.Value) - CDbl(TextBox90.Value)) * CDbl(TextBox86.Value), "0.0000")
    Me.EnableEvents = True
End Sub
 
Upvote 0
Hi Andrew,

Thanx for the code but I still get the same Error MSG
Maybe I am putting the code in the wrong place or maybe I have to define something first ???

I got this from the help in excel :

[TABLE="width: 100%"]
<tbody>[TR]
[TD="align: left"]Method or data member not found (Error 461)[/TD]
[/TR]
</tbody>[/TABLE]

The collection, object, or user-defined type doesn't contain the referenced member. This error has the following causes and solutions:

  • You misspelled the object or member name. Check the spelling of the names and check the Type statement or the object documentation to determine what the members are and the proper spelling of the object or member names.
  • You specified a collection index that's out of range. Check the Count property to determine whether a collection member exists. Note that collection indexes begin at 1 rather than zero, so the Count property returns the highest possible index number.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).


Andrew Poulsom said:
Try:

Rich (BB code): 
Public EnableEvents As Boolean

Private Sub UserForm_Initialize()
    Me.EnableEvents = True
End Sub

Private Sub TextBox135_Change()
    If Me.EnableEvents = False Then Exit Sub
    Me.EnableEvents = False
    On Error Resume Next
    TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
    TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
    TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
    TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
    Me.EnableEvents = True
End Sub

Private Sub TextBox136_Change()
    If Me.EnableEvents = False Then Exit Sub
    Me.EnableEvents = False
    On Error Resume Next
    TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
    TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
    TextBox138.Value = Format(CDbl(TextBox136.Value) - CDbl(TextBox90.Value), "0.0000")
    TextBox135.Value = Format((CDbl(TextBox137.Value) - CDbl(TextBox90.Value)) * CDbl(TextBox86.Value), "0.0000")
    Me.EnableEvents = True
End Sub
Click to expand...
 
Last edited:
Upvote 0
Hi,

I am trying a new approach for the problem it worked with me on the work sheet but I don't know how to set it for the user form.
I used If statement to check for each cell and then execute calculation depending on which if it is true:

I tried to modify it to fit for the user form use but I think it needs some touches to work :

Sub calculate()


End Sub
If Target = TextBox135.Value Then
On Error Resume Next
TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")

ElseIf Target = TextBox136.Value Then
On Error Resume Next
TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")

End If

' Enable Events again
Application.EnableEvents = True
End Sub


Private Sub user_form_SelectionChange(ByVal Target As Range)


End Sub
Click to expand...
 
Upvote 0
myfe said:
Hi Andrew,

Thanx for the code but I still get the same Error MSG

Rich (BB code): 
Public EnableEvents As Boolean

Private Sub UserForm_Initialize()
    Me.EnableEvents = True
End Sub

Private Sub TextBox135_Change()
    If Me.EnableEvents = False Then Exit Sub
    Me.EnableEvents = False
    On Error Resume Next
    TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
    TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
    TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
    TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
    Me.EnableEvents = True
End Sub

Private Sub TextBox136_Change()
    If Me.EnableEvents = False Then Exit Sub
    Me.EnableEvents = False
    On Error Resume Next
    TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
    TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")
    TextBox138.Value = Format(CDbl(TextBox136.Value) - CDbl(TextBox90.Value), "0.0000")
    TextBox135.Value = Format((CDbl(TextBox137.Value) - CDbl(TextBox90.Value)) * CDbl(TextBox86.Value), "0.0000")
    Me.EnableEvents = True
End Sub
Click to expand...
EnableEvents is a property of the Application object... it is not a property of Me (the UserForm). Try replacing the red highlighted lines of code with this single line of code...

Rich (BB code): 
Application.EnableEvents = False
And replace the lines highlighted in blue with this line of code...

Rich (BB code): 
Application.EnableEvents = True
 
Upvote 0
Thanx Rick,

But I still get compile error: "method or Data member not found" :(


Can you please help me with the other approach.
I think it is much easier and straightforward though not efficient but at this stage I just want the code to work.

Can you help me to make this if statement work for the user form so once an input in any of the text box is entered the if\else statement checks for the active text box and input the calculation in the empty text box. (It worked with the excel sheet but I don't know how to change it to work for the user form).


Sub calculate()


End Sub
If Target = TextBox135.Value Then
On Error Resume Next
TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")

ElseIf Target = TextBox136.Value Then
On Error Resume Next
TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")
TextBox136.Value = Format(CDbl(TextBox90.Value) + CDbl(TextBox138.Value), "0.0000")
TextBox137.Value = Format(CDbl(TextBox136.Value) / CDbl(TextBox21.Value), "0.0000")
TextBox134.Value = Format(CDbl(TextBox137.Value) - CDbl(TextBox85.Value), "0.0000")

End If

' Enable Events again
Application.EnableEvents = True
End Sub


Private Sub user_form_SelectionChange(ByVal Target As Range)


End Sub
Click to expand...
 
Upvote 0
Rick Rothstein said:
EnableEvents is a property of the Application object... it is not a property of Me (the UserForm). Try replacing the red highlighted lines of code with this single line of code...

Code: 
Application.EnableEvents = False
And replace the lines highlighted in blue with this line of code...

Code: 
Application.EnableEvents = True
Click to expand...

EnableEvents is a boolean, declared at the top of the UserForm module:

Code: 
Public EnableEvents As Boolean
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Error 1004
2
Replies
18
Views
275
dmt32
D
P
  • Solved
Encountering Runtime Error When Entering Date in Textbox
Replies
2
Views
507
pmgibs
P
E
  • Question Question
VBA code clean up
Replies
9
Views
478
mumps
mumps
R
  • Solved
Date format in userform text box on double click.
Replies
9
Views
2K
Rfriend
R
A
  • Question Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
887
auglocqnuk
A

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top