Pressing Enter on Control to Refresh

[CWit]

Hello ,on sheet 1 I have 3 textboxcontrols that are linked to cells o
sheet 2, When I input data into the text boxes and press enter the cel
changes but the formulas won't update. So I need to refresh sheet
upon hitting Enter?? Thanks for the help

 

[Tom Ogilvy]

Set calculation to Automatic

 

[CWit]

Tom,

The Workbook is set to automatic, the problem I think is that th
formula is not contained in the cells but in VBA code in the worksheet
so for some reason It changes the value of the cell but not until
physically go to Sheet 2 change the cell and press enter, it won'
update.

Thank

 

[Tom Ogilvy]

Sounds like your code is causing an error. It is possible the code is
written so it only works correctly when your sheet is the active sheet.
Also, make sure you don't have the code in a sheet module. It should be in
a general module (insert=>module in the VBE).

 

[CWit]

Well, I have 2 sheets, Sheet one is a Visual Input, what the client wil
see, sheet 2 contains the info. On sheet 1 I have 5 Textbox control
that are linked to Cells A1:A5 on sheet 2 here is the code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A5")) Is Nothing Then
Application.EnableEvents = False
Select Case .Address(False, False)
Case "A1"
Range("A3") = Range("A2").Value * .Value / 100
Range("A5") = .Value * Range("A4").Value / 100
Case "A2"
Range("A3").Value = .Value * Range("A1").Value / 100
Range("A4") = 100 - Range("A2").Value
Range("A5") = Range("A4").Value * Range("A1").Value / 100
Case "A3"
Range("A1").Value = .Value / Range("A2").Value * 100
Range("A5").Value = Range("A1").Value * Range("A4").Value / 100
Case "A4"
Range("A2") = 100 - Range("A4").Value
Range("A3").Value = .Value * Range("A1").Value / 100
Range("A5") = Range("A4").Value * Range("A1").Value / 100
Case Else
Range("A1").Value = .Value / Range("A4").Value * 100
Range("A3").Value = Range("A1").Value * Range("A2").Value / 100
End Select
Application.EnableEvents = True
End If
End With
End Sub


Any help would be great thanks

 

[Tom Ogilvy]

Making a change in the textbox does not trigger the change event for sheet2.

You will have trigger your code using some other method. Perhaps using
events for the textboxes.