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

 

[DanteAmor]

Can't read your code.
You can put the code between these tags (select # code tag icon):

Private Sub Worksheet_Change(ByVal Target As Range)

 

[ed.ayers315]

Sorry

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

 

[DanteAmor]

Now where are you going to get this "TextBox117"

 

[ed.ayers315]

Thanks for checking this out Dante,

I don't want to use a text box anymore. The text box was in a user form I made but the other users dis not let to hassle with it and want to just enter a number in those cells (c29, c35, c42, e29, e35, e42, g29, g35, g42, i29, i35, i43) in the worksheet and have the vba ask the user after hitting enter if the number is for ozs/gal or % caustic and then do the math and format in the cell directly under the one above.

 

[DanteAmor]

You have
c29.Value = Round(CDbl(TextBox117.Value) * 7812.5, 0) & " PPM"

Now

c29.Value = Round(CDbl(??????) * 7812.5, 0) & " PPM"

 

[ed.ayers315]

Struggling!

The code above is meant to only run the calculations if the particular cells are changed, currently it does not matter what cell I make a change it call the message box to enter the choice and then formats the target cell in the code. It does not place the calculated and formatted number in the cell below.

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
                c30.Value = Round(CDbl(c29.Value) * 7812.5, 0) & " PPM"
                c29.Value = Format(c29.Value, "0.0 ozs/gal")
                ActiveSheet.Unprotect
                Range("$c$29").Select
'                Selection.NumberFormat = "General"
                Selection.NumberFormat = "0.0"" ozs/gal"""
                c30.Value = Round(CDbl(c29.Value) * 7812.5, 0) & " PPM"
                ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            Case 2




'                                Do stuff for choice 2 here
                c30.Value = Round(CDbl(c29.Value) * 10000, 0) & " PPM"
                c29.Value = Format(c29.Value / 100, "0.0 %")
                ActiveSheet.Unprotect
                Range("$c$29").Select
'                Selection.NumberFormat = "General"
                Selection.NumberFormat = "0.0"" %"""
                c30.Value = Round(CDbl(c29.Value) * 10000, 0) & " PPM"
                ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            End Select


                On Error GoTo 0


            End If


End Sub

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim UR7 As Variant, UserResp7 As Variant
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("c29, e29, g29, i29, c35, e35, g35, i35, c42, e42, g42, i42")) 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")
      If UserResp7 = "" Or UserResp7 = False Then Exit Sub
      UR7 = Val(UserResp7)
    Wend
    ActiveSheet.Unprotect
    Application.EnableEvents = False
    On Error GoTo Enable_Events
    Select Case UR7
      Case 1
        Target.Offset(1).Value = Round(CDbl(Target.Value) * 7812.5, 0) & " PPM"
        Target.NumberFormat = "0.0"" ozs/gal"""
      Case 2
        Target.Offset(1).Value = Round(CDbl(Target.Value) * 10000, 0) & " PPM"
        Target.Value = Target.Value / 100
        Target.NumberFormat = "0.0"" %"""
      End Select
      ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  End If
Enable_Events:
    Application.EnableEvents = True
End Sub

 

[ed.ayers315]

the math is working! the formatting is not.

when I change any other cell the message box pops up and requires the input 1 or 2. I need the change event to only apply to C29, c35, c42; e29, e35, e42, g29, g35, g42, i29, i35, and i42.

Thanks for your help!

 

[DanteAmor]

the math is working! the formatting is not.

What should appear in case 1 and case 2

when I change any other cell the message box pops up and requires the input 1 or 2. I need the change event to only apply to C29, c35, c42; e29, e35, e42, g29, g35, g42, i29, i35, and i42.

Thanks for your help!

Change the cells you want in this line:

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