MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Jamil    Date: Feb 12    Category: MS Office    Views: 2115

I am doing a calculation use VBA form which will directly do
calculation and update the excel using userform.

It does that, but the only code I need is to reflect the changes as
soon as I hit Calculate or Update button. The mistake in the form is
it does the calculation but doesn't update the form unless the user
close and reopens the form.

Private Sub Frame1_Click()

'Private Sub CommandButton1_Click()

Option Explicit

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

End Sub

Private Sub CommandButton2_Click()

'to enter the data

Sheets("data").Range("F4") = TextBox1.Value
Sheets("data").Range("F5") = TextBox2.Value
'Sheets("data").Range("F6") = TextBox3.Value

End Sub

Private Sub TextBox2_Change()

End Sub
Private Sub TextBox3_Change()

End Sub
Private Sub UserForm_Activate()

'to display data

TextBox1.Value = Sheets("data").Range("F4")
TextBox2.Value = Sheets("data").Range("F5")


TextBox6.Value = Sheets("data").Range("F6")



5 Answers Found

Answer #1    Answered By: Utsav Shah     Answered On: Feb 12

I believe you may need to add the following line in your logic:

Application DoEvents

Not sure exaclty where!

Answer #2    Answered By: Ziza Mizrachi     Answered On: Feb 12

I believe whay rinks needs is a method of entering a value in a cell and
at the same time getting the calculation  in the form  done.

I guess this is not possible in conventional form built mode but I guess
thru control toolbox mode

Doevents will not allow access to keyboard.

Answer #3    Answered By: Fairuzah Alam     Answered On: Feb 12

Sorry to be blunt, but your code  is very hard to understand.
It seems to have nested subs, some of which do nothing.

You said "as soon as I hit  Calculate or update  button", but
I don't see an calculate  or Update... I see what looks like an
"add" button, but all it does is sets the variable "ws" as
the worksheet called "data", then exits. ws is defined within the
sub, so it isn't even available outside the sub, nor is it used anywhere else.
I don't see ANY code that does any type of calculation.
The only thing I DO so is the commandbutton2 event that copies the
values to the sheet.

The answer to your question though is

Textbox3.value = textbox1.value + textbox2.value

You can also add this to the textbox_Change event to continually update
the value in textbox  3 without the need to hit a "calculate" button.

will add the contents of the first two boxes and put the result in the third
If you put this in whatever click event you use for the "calculate" button,
it should display  the result in box 3...

hope this helps,

Answer #4    Answered By: Gerardo Morgan     Answered On: Feb 12

Can i send the file directly to you, if
you can see how to add codes for update  push button

Answer #5    Answered By: Kawakib Mansour     Answered On: Feb 12

Move the code  which updates the userform  on open to a separate
sub/function. Call the sub on activation and then call the sub after
you do the calculation.

Didn't find what you were looking for? Find more on VBA TEXTBOX Or get search suggestion and latest updates.