Solved: Update Userform after changes have been made on another useform

[Darren]

Hi Captains

Thanks extended to Bob and Malcolm for there helpfulness in this small project

I have a Userform Update problem.

I have a userform called "OverHeads"
and a userform called TheBasicFormulas"

I enter information in OverHeads and then Click on Basic Formulas and that works perfectly. I then go back to OverHeads and make a change.... I then click on Basic Formulas and it has not updated the information on this userform.

Its like a need a refresh on open piece of code

On Opening Overheads

[VBA]Private Sub CommandButton1_Click()
Hide
OverHeads.Show
End Sub[/VBA]
Would like it to check userform if any changes have been made before proceeding.

On opening Basic Terms

[VBA]Private Sub CommandButton2_Click()
Hide
TheBasicFormulas.Show
End Sub[/VBA]

Would like it to check if any changes have been made to the userform before proceding

I hope you can help thanks guys and ladies

Darren
South Africa

[mdmackillop]

Hi Darren
Maybe you could add a public variable set to True when changes are made. Test for this from either form and update your forms accordingly.

[Bob Phillips]

Why bother checking, just update

[vba]

Private Sub CommandButton2_Click()
Hide
TheBasicFormulas.Label1.Caption = TextBox1.Text
'etc.
TheBasicFormulas.Show
End Sub
[/vba]

[Darren]

Hi Bob & Malcolm

Will test this and report thanking you both

Darren
South Africa

[tpoynton]

Recently started a project where I do this; I do both of what Bob and MD suggest; update values directly on the userform, and set a public variable to false if there are no changes or the user cancels. in this case, it seems like a public variable may not be necessary, as you can just test for changes on the original userform.

might want to post a sample if you cant get it working like you want...

[Darren]

Hi Captains

Bob i tried the code posted and it works but then a part of the of the code on TheBasicFomulas stops working. Will put a detailed report together now and post a before and after i place the code in the CommandButton2_click

Full report to follow

Darren

[Darren]

Hi Captains

I have posted a my complete working Sample. VBA Express shares with me so i share with all.

To understand the problem.... Enter information in the boxes as if it was your business...

Then

Go back to Basic Terms and change something.... say a different turnover and royalty amount .... it must update the Overheads sheet it does .

Enter your Overheads and then click on Basic Formulas..... Foodcost is the active cell enter your cost here that you have spent to create the turnover and press enter. You will see the OverHeads is carried forward and the other boxes are automatically filled.

PROBLEM
When i go back to my overheads by clicking on the CommandBox at the bottom and i change an amount say Rent to another figure it does not update the figures on BasicFormulas and does not update nett and gross % it should also update Summary.

I hope you can fix this i have tried all day and hit a blank, I hope this will be usefull to others once complete.

To summarize if you complete the userforms once and go back to other userforms to change something it must update the others.

Thanking You

Darren
South Africa

[geekgirlau]

You could call any procedure that references a different user form in the Userform Activate event.

For example, in the form "TheBasicFormulas" the procedure "CFoodcost_AfterUpdate" has a reference to a control on the "Overheads" form. If you add "CFoodcost_AfterUpdate" to the Userform_Activate event for "TheBasicFormulas", any new values will be updated each time the form is activated.

[Darren]

Hi Geekgirl

Thank you for the reply. Have tested and it works ok and updates as it should.

The problem is on the "The Basic Formulas" Userform with the following boxes not firing or should i say filling in the relevant information

After you have filled in the amount for Food Cost and press enter the cursor moves down to Total Cost this amount is pulled from the OverHeads userform this is correct. But these are empty and should autofill

NettProfit box is empty
NettProfit % box is empty
NettProfit box is empty
Turnover x 100 is empty
Breakevenpoint is empty

I can get them to fill by using this process ..... click back on the Overheads userform and change one of the amounts say Rent and then click back on the Basic Formulas they then autofill. ??

Is there a way to fire the above boxes after the Food Cost amount has been entered.

This is the code for the TheBasicFormulas
[VBA]Private Sub CommandButton5_Click()
Me.PrintForm
End Sub
Private Sub CommandButton6_Click()
Hide
Summary.Show
End Sub
Private Sub UserForm_Activate()
lblBasicInFormulas = BasicTerms.lblTurnoverExcl.Caption
BTurnover = TheBasicFormulas.lblBasicInFormulas.Caption
CFoodcost_AfterUpdate
FTotalCost_AfterUpdate


End Sub
Private Sub CommandButton1_Click()
Hide
SalesTaxCalc.Show
End Sub
Private Sub CommandButton2_Click()
Hide
BasicTerms.Show
End Sub
Private Sub CommandButton3_Click()
Hide
OverHeads.Show
End Sub
Private Sub CommandButton4_Click()
Hide
PromoCalc.Show
End Sub
Private Sub CFoodcost_AfterUpdate()
CFoodcost = Format(CFoodcost, "#,##0.00")
If CFoodcost.Text <> "" Then
End If
FTotalCost = OverHeads.TextBox15.Text
FTotalCost_AfterUpdate
End Sub

Private Sub CFoodcost_Change()
If CFoodcost.Text <> "" Then
If BTurnover.Text <> "" Then

AGross = Format(BTurnover - CFoodcost, "#,##0.00")
EGrossProfit = AGross

End If
End If

End Sub


Private Sub FTotalCost_Change()
If FTotalCost.Text <> "" Then
If EGrossProfit.Text <> "" Then
DNettProfit = Format(EGrossProfit - FTotalCost, "#,##0.00")
HNettProfit = DNettProfit
ITurnover = BTurnover
GPercentNett = Format(HNettProfit / ITurnover, "0.00%")
JBreakEvenpoint = Format(FTotalCost * BTurnover / AGross, "#,##0.00")
End If
End If


End Sub
Private Sub FTotalCost_AfterUpdate()
If FTotalCost.Text <> "" Then
FTotalCost = Format(FTotalCost, "#,##0.00")

End If
End Sub
[/VBA]

Thanking you for you help and attention

Darren
South Africa

[Darren]

Hi
This the result after FoodCost have been input and then pressing enter

[Darren]

Hi Captains

Worked it out and the code is as follows

to fix the update problem

[VBA]
Private Sub CommandButton1_Click()
Hide
SalesTaxCalc.Show
End Sub
Private Sub CommandButton5_Click()
Me.PrintForm
End Sub
Private Sub CommandButton6_Click()
Hide
Summary.Show
End Sub
Private Sub CommandButton7_Click()
Hide
DailyControl.Show
End Sub
Private Sub PercentFoodCost_AfterUpdate()
PercentFoodCost = Format(PercentFoodCost, "#,##0.00%")
End Sub
Private Sub PercentFoodCost_Change()
PercentFoodCost = Format(CFoodcost / ITurnover, "0.00%")
End Sub
Private Sub PercentGross_AfterUpdate()
PercentGross = Format(PercentGross, "#,##0.00%")
End Sub
Private Sub PercentGross_Change()
PercentGross = Format(AGross / ITurnover, "#,##0.00%")
End Sub
Private Sub UserForm_Activate()
lblBasicInFormulas = BasicTerms.lblTurnoverExcl.Caption
BTurnover = TheBasicFormulas.lblBasicInFormulas.Caption
CFoodcost_AfterUpdate
FTotalCost_AfterUpdate
PercentFoodCost_AfterUpdate
PercentGross_AfterUpdate



End Sub

Private Sub CommandButton2_Click()
Hide
BasicTerms.Show
End Sub
Private Sub CommandButton3_Click()
Hide
OverHeads.Show
End Sub
Private Sub CommandButton4_Click()
Hide
PromoCalc.Show
End Sub
Private Sub CFoodcost_AfterUpdate()
CFoodcost = Format(CFoodcost, "#,##0.00")
If CFoodcost.Text <> "" Then
End If
FTotalCost = OverHeads.TextBox15.Text
FTotalCost_AfterUpdate
End Sub

Private Sub CFoodcost_Change()
If CFoodcost.Text <> "" Then
If BTurnover.Text <> "" Then


AGross = Format(BTurnover - CFoodcost, "#,##0.00")
EGrossProfit = AGross


End If
End If



End Sub


Private Sub FTotalCost_Change()
If FTotalCost.Text <> "" Then
If EGrossProfit.Text <> "" Then
DNettProfit = Format(EGrossProfit - FTotalCost, "#,##0.00")
HNettProfit = DNettProfit
ITurnover = BTurnover
GPercentNett = Format(HNettProfit / ITurnover, "0.00%")
JBreakEvenpoint = Format(FTotalCost * BTurnover / AGross, "#,##0.00")
PercentFoodCost = Format(CFoodcost / ITurnover, "0.00%")
PercentGross = Format(AGross / ITurnover, "#,##0.00%")

End If
End If


End Sub
Private Sub FTotalCost_AfterUpdate()
If FTotalCost.Text <> "" Then
FTotalCost = Format(FTotalCost, "#,##0.00")

End If

FTotalCost_Change
End Sub[/VBA]

thanks to those who helped Malcolm, Bob and geekgirl

Darren
South Africa

[mdmackillop]

Hi Darren,
Glad you've got this solved. In your next project though, I would suggest you look at one MultiPage form rather than separate UserForms. It keeps all your controls accessible and avoids your updating problems.
Regards
Malcolm

[Darren]

Hi Malcolm

Thanks for the reply, I hope others will find the program useful. New word ALERT ...... MultiPage ...... form. shew more reading but will look it up or do you have knowledge based reference for me to study.

Thanks for your assistance

Darren
South Africa

[mdmackillop]

You'll find Multipage on the Userfom toolbox. I'm sure you'll find it useful.

[Darren]

Malc

Thanks a whole new Toy

Cheers
Darren