VBA Calculation and Formatting problem

[ed.ayers315]

Hello,

I want to enter a number in these cells (c29, c35, c42, e29, e35, e42, g29, g35, g42, i29, i35, i43) after clicking out of that cell or pressing enter I want to perform the below calculations based on my choice in the dialog box.

I had this working in a userform using text box references. My user (trainee) said it was too confusing so I am trying to do it in the worksheet.

Any help will be appreciated! Thanks

Ed

'Private Sub Worksheet_Change(ByVal Target As Range)''On Error Resume Next'''If Not Intersect(Selection(c29, e29, g29, g29, c35, e35, g35, i35, c42, e42, g42, i42), Target) Is Nothing Then''        Prompt = "1. This is your first choice" & vbLf & vbLf & "Press 1 for: Are you Titrating for ozs/Gal" & vbLf'        Prompt = Prompt & "Press 2 for. Are you Titrating for %" & vbLf'        UR7 = 0'        While UR7 < 1 Or UR7 > 5'            UserResp7 = InputBox(Prompt, "The Big Question")'            UR7 = Val(UserResp7)'        Wend'        Select Case UR7'            Case 1''                                Do stuff for choice 1 here'                c29.Value = Round(CDbl(TextBox117.Value) * 7812.5, 0) & " PPM"'                c29.Value = Format(TextBox117.Value, "0.0 ozs/gal")'                ActiveSheet.Unprotect'                Range("$c$30").Select'                Selection.NumberFormat = "General"'                Selection.NumberFormat = "0.0"" ozs/gal"""'                ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True'            Case 2''''                                Do stuff for choice 2 here'                c29.Value = Round(CDbl(TextBox117.Value) * 10000, 0) & " PPM"'                c29.Value = Format(TextBox117.Value / 100, "0.0 %")'                ActiveSheet.Unprotect'                Range("$c$30").Select'                Selection.NumberFormat = "General"'                Selection.NumberFormat = "0.0"" %"""'                ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True'            End Select''                On Error GoTo 0''            End If''End Sub

 

[ed.ayers315]

Thanks for trying but nothing seems to be working!

Enjoy your Saturday!

 

[DanteAmor]

I'm not understanding.

You said:

"the formatting is not."


Then explain what to appear and what should appear?

 

[ed.ayers315]

Dante,

Thanks for not giving up!

When a 1 is entered into c29, the message box pops up and asks the user to make the choice of a 1 for "ozs/gal" or 2 for "%". I want the macro to format the 1 to ozs/gal when one is selected and place the math and formatted number into c30 "ppm"

When 1 is entered and one selected in the pop up; c29 should be 1.0 ozs/gal and c30 should be formula result of c29 * 7812.5 formatted to PPM. If 1.5 is typed into c29 and option 1 selected c29 would appear 1.5 ozs/gal and c30 would be 11,718 PPM.

When 1 is enter into c29 and the option 2 is selected, c29 appears as 1.0 % and c30 would be 10000 PPM because when option two is selected it multiplies the entered number in c29 by 10000. so in this case if 1.5 is entered in c29, c29 would be 1.5 % and c30 should be 15000 PPM.

Again, these inputs and the formula results must stay in the cells after the operation is completed.

Again, this macro should only run if one of these cells have an edit entered:

[COLOR=#333333]If Not Intersect(Target, Range("c29, e29, g29, i29, c35, e35, g35, i35, c42, e42, g42, i42")) Is Nothing Then[/COLOR]

Sorry for being a pain!

 

[DanteAmor]

Again, this macro should only run if one of these cells have an edit entered:

[COLOR=#333333]If Not Intersect(Target, Range("c29, e29, g29, i29, c35, e35, g35, i35, c42, e42, g42, i42")) Is Nothing Then[/COLOR]

!

well, those are the cells that were in your original macro. Then tell me in which cells you want the operation.

 

[ed.ayers315]

These those are, but only one should change. When I enter into c35, it changes the format in c29.

What ever happens in any one of those cells, should remain regardless of any other changes.

 

[DanteAmor]

Do you have another macro running? or a conditional format?

 

[ed.ayers315]

There was conditional formatting but not another macro.

Once I got rid of the conditional formatting; the ozs/gal (choice 1) works perfect. Choice 2 works for the PPM part but the entry into the referenced cell above loses the number, this is what remains 0.0%. The actual value in the cell is 0.01.

Almost there!!!

 

[ed.ayers315]

I got it!

It was this part of the code "Target.Value = Target.Value / 100"

Thanks so much for all your help!!!!

 

[DanteAmor]

I got it!

It was this part of the code "Target.Value = Target.Value / 100"

Thanks so much for all your help!!!!

I'm glad to help you. Thanks for the feedback.