VBA Calculation and Formatting problem

ed.ayers315

Board Regular
Joined
Dec 14, 2009
Messages
166
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


HTML:
'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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Can't read your code.
You can put the code between these tags (select # code tag icon):


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
Upvote 0
Sorry

Code:
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
 
Last edited:
Upvote 0
Now where are you going to get this "TextBox117"
 
Upvote 0
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.
 
Upvote 0
You have
c29.Value = Round(CDbl(TextBox117.Value) * 7812.5, 0) & " PPM"

Now

c29.Value = Round(CDbl(??????) * 7812.5, 0) & " PPM"
 
Upvote 0
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.

Code:
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
 
Upvote 0
Try this

Code:
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
 
Upvote 0
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!
 
Upvote 0
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:

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

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top