textbox results and custom format with vba

ed.ayers315

ed.ayers315

Board Regular
Joined
Dec 14, 2009
Messages
166
Hi folks,

I have tried numerous versions of the code below; some from this forum and some some the VBForums.

When I exit textbox177, I need the input to format to the Vessels c29 exactly. Then as seen below take that number and depending on the case selection do the correct math. The math seems to be working just fine. It is the orginal input that I continue to get screwed up.

Either the text box on the form does not show the correct format or the cell on the worksheet shows the wrong or no format, and somehow both.

I have 12 different combination of these on one form.



Code: 
Private Sub TextBox177_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim Prompt As String
    Dim UserResp2 As String
    Dim UR2 As Integer




            If IsNumeric(TextBox177.Text) Then
                If IsNumeric(TextBox177.Text) Then
                    If IsNumeric(TextBox177.Text) Then


                        Prompt = "1. This is your first choice" & vbCrLf & vbCrLf & "Press 1 for: Are you Titrating for ozs/Gal" & vbCrLf
                        Prompt = Prompt & "Press 2 for. Are you Titrating for %" & vbCrLf


                        UR2 = 0
                        While UR2 < 1 Or UR2 > 5
                            UserResp2 = InputBox(Prompt, "The Big Question")
                                 UR2 = Val(UserResp2)
                        Wend
                        Select Case UR2
                            Case 1


'                                Do stuff for choice 1 here
                                TextBox173.Value = Round(CDbl(TextBox177.Value) * 7812.5, 0) & " PPM"
                                Sheets("VESSELS").Range("$c$29") = TextBox177
                                    Sheets("VESSELS").Select
                                    ActiveSheet.Unprotect
                                    Range("$c$29").Select
                                    Selection.NumberFormat = "General"
                                    Range("$c$29").Select
                                    Selection.NumberFormat = "#.0"" ozs/gal"""
                                    ActiveSheet.Protect
'
                            Case 2


'                                Do stuff for choice 2 here
                                TextBox173.Value = Round(CDbl(TextBox177.Value) * 10000, 0) & " PPM"
                                Sheets("VESSELS").Range("$c$29") = TextBox177
                                    Sheets("VESSELS").Select
                                    ActiveSheet.Unprotect
                                    Range("$c$29").Select
                                    Selection.NumberFormat = "General"
                                    Range("$c$29").Select
                                    Selection.NumberFormat = "#.0"" %"""
                                    ActiveSheet.Protect
'
                            End Select
                   End If
            End If
        End If
        
End Sub
Private Sub TextBox25_Exit(ByVal Cancel As MSForms.ReturnBoolean)


    Dim Prompt As String
    Dim UserResp4 As String
    Dim UR4 As Integer




            If IsNumeric(TextBox25.Text) Then
                If IsNumeric(TextBox25.Text) Then
                    If IsNumeric(TextBox25.Text) Then


                        Prompt = "1. This is your first choice" & vbCrLf & vbCrLf & "Press 1 for: Are you Titrating for ozs/Gal" & vbCrLf
                        Prompt = Prompt & "Press 2 for. Are you Titrating for %" & vbCrLf


                        UR4 = 0
                        While UR4 < 1 Or UR4 > 5
                            UserResp4 = InputBox(Prompt, "The Big Question")
                                 UR4 = Val(UserResp4)
                        Wend
                        Select Case UR4
                            Case 1


'                                Do stuff for choice 1 here
                                TextBox53.Value = Round(CDbl(TextBox25.Value) * 7812.5, 0) & " PPM"
                                Sheets("VESSELS").Range("$c$42") = TextBox25
                                    Sheets("VESSELS").Select
                                    ActiveSheet.Unprotect
                                    Range("C42").Select
                                    Selection.NumberFormat = "General"
                                    Selection.NumberFormat = "#.#"" ozs/gal"""
                                    ActiveSheet.Protect
'
                            Case 2


'                                Do stuff for choice 2 here
                                TextBox53.Value = Round(CDbl(TextBox25.Value) * 10000, 0) & " PPM"
                                Sheets("VESSELS").Range("$c$42") = TextBox25
                                    Sheets("VESSELS").Select
                                    ActiveSheet.Unprotect
                                    Range("C42").Select
                                    Selection.NumberFormat = "General"
                                    Selection.NumberFormat = "#.#"" %"""
                                    ActiveSheet.Protect
'
                            End Select
                   End If
            End If
        End If
        
End Sub
 
Sort by date Sort by votes
Hi
I'm not sure if this is what you want; if not, please provide a complete example of sample inputs and desired outputs, including numerical values and formats...

Code: 
Private Sub TextBox177_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Dim Prompt As String, UserResp2 As String, UR2%


If IsNumeric(TextBox177.Text) 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
        UR2 = 0
        While UR2 < 1 Or UR2 > 5
            UserResp2 = InputBox(Prompt, "The Big Question")
            UR2 = Val(UserResp2)
        Wend
        Select Case UR2
            Case 1
'                                Do stuff for choice 1 here
                TextBox173.Value = Round(CDbl(TextBox177.Value) * 7812.5, 0) & " PPM"
                ActiveSheet.Unprotect
                Sheets("VESSELS").Activate
                Range("c29").Value = Format(TextBox177.Value, "#.0") & " ozs/gal"
                ActiveSheet.Protect
            Case 2


'                                Do stuff for choice 2 here
                TextBox173.Value = Round(CDbl(TextBox177.Value) * 10000, 0) & " PPM"
                ActiveSheet.Unprotect
                Sheets("VESSELS").Activate
                Range("c29").Value = Format(TextBox177.Value, "0.0%")
                ActiveSheet.Protect
            End Select


End If
        
End Sub
 
Upvote 0
Hi Worf

Thanks for your reply. The code works on the user form for textbox173 formatted with "PPM". It does not format textbox177 or c29 on the sheet.

I would like these all to have the same number format, whether it is "%" or "ozs/gal".
 
Upvote 0
Please test this one:

Code: 
Private Sub TextBox177_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Dim Prompt$, UserResp2$, UR2%


If IsNumeric(TextBox177.Text) 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
        UR2 = 0
        While UR2 < 1 Or UR2 > 5
            UserResp2 = InputBox(Prompt, "The Big Question")
            UR2 = Val(UserResp2)
        Wend
        Select Case UR2
            Case 1
'                                Do stuff for choice 1 here
                TextBox173.Value = Round(CDbl(TextBox177.Value) * 7812.5, 0) & " PPM"
                TextBox177.Value = Format(TextBox177.Value, "#.0") & " ozs/gal"
                Sheets("VESSELS").Activate
                ActiveSheet.Unprotect
                Range("c29").Value = TextBox177.Value
                ActiveSheet.Protect
            Case 2


'                                Do stuff for choice 2 here
                TextBox173.Value = Round(CDbl(TextBox177.Value) * 10000, 0) & " PPM"
                TextBox177.Value = Format(TextBox177.Value, "#.0") & " %"
                Sheets("VESSELS").Activate
                ActiveSheet.Unprotect
                Range("c29").Value = TextBox177.Value
                ActiveSheet.Protect
            End Select


End If
        
End Sub
 
Upvote 0
Hi Worf

what does the Prompt$, UserResp2$, and UR2% actually do

i have not tried it yet, traveling today, I will and let you know

Thanks- Happy Holidays


Worf said:
Please test this one:

Code: 
Private Sub TextBox177_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Dim Prompt$, UserResp2$, UR2%


If IsNumeric(TextBox177.Text) 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
        UR2 = 0
        While UR2 < 1 Or UR2 > 5
            UserResp2 = InputBox(Prompt, "The Big Question")
            UR2 = Val(UserResp2)
        Wend
        Select Case UR2
            Case 1
'                                Do stuff for choice 1 here
                TextBox173.Value = Round(CDbl(TextBox177.Value) * 7812.5, 0) & " PPM"
                TextBox177.Value = Format(TextBox177.Value, "#.0") & " ozs/gal"
                Sheets("VESSELS").Activate
                ActiveSheet.Unprotect
                Range("c29").Value = TextBox177.Value
                ActiveSheet.Protect
            Case 2


'                                Do stuff for choice 2 here
                TextBox173.Value = Round(CDbl(TextBox177.Value) * 10000, 0) & " PPM"
                TextBox177.Value = Format(TextBox177.Value, "#.0") & " %"
                Sheets("VESSELS").Activate
                ActiveSheet.Unprotect
                Range("c29").Value = TextBox177.Value
                ActiveSheet.Protect
            End Select


End If
        
End Sub
Click to expand...
 
Upvote 0
The following statements are equivalent, two ways to declare variables:

Dim a as String, b as Integer

and

Dim a$, b%

Happy Holidays!
 
Upvote 0
Worf said:
The following statements are equivalent, two ways to declare variables:

Dim a as String, b as Integer

and

Dim a$, b%

Happy Holidays!
Click to expand...

Thanks for hanging in there.

I played with this code for hours yesterday. Through many variations including your original version. I could get one text box and one row in column c to show the write format but then not the other.

I am not giving up just had to travel for hours
 
Upvote 0
Hi Worf,

Hope you and your's had a great holiday as well as the forum goers.

I could not get your suggestion to work in all cases, however; the following code is working as intended. I could not have arrived at the solution without your input and advise.

<<Private Sub TextBox177_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next


Dim Wash2Circuit1 As String
Wash2Circuit1 = Range("$c$35").Value


If IsNumeric(TextBox177.Text) 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
UR2 = 0
While UR2 < 1 Or UR2 > 5
UserResp2 = InputBox(Prompt, "The Big Question")
UR2 = Val(UserResp2)
Wend
Select Case UR2
Case 1
' Do stuff for choice 1 here
TextBox173.Value = Round(CDbl(TextBox177.Value) * 7812.5, 0) & " PPM"
TextBox177.Value = format(TextBox177.Value, "0.0 ozs/gal")
ActiveSheet.Unprotect
Range("$c$35").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
TextBox173.Value = Round(CDbl(TextBox177.Value) * 10000, 0) & " PPM"
TextBox177.Value = format(TextBox177.Value / 100, "0.0 %")
ActiveSheet.Unprotect
Range("$c$35").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>>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
200
MeaclH
M
A
  • Question Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
925
auglocqnuk
A
ed.ayers315
  • Question Question
VBA Calculation and Formatting problem
2
Replies
18
Views
2K
DanteAmor
DanteAmor
M
  • Question Question
Making cells have the same behaviour as control textbox based on dropdown list
Replies
0
Views
748
maxipoblete
M
M
  • Question Question
Trouble with leading 0s in VBA
Replies
3
Views
722
Rick Rothstein
Rick Rothstein

Share this page

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