VBA to go to next number after decimal place reaches a certain value

[07gashbridge]

Hell hive mind, hope you're all well,

I've got a user form for adding in multiple set's of user defined data across multiple rows in a sheet from their inputted data in the form fields. The form also has a function to repeat each data entry depending on how many times the user specifies for there to be copies of their inputted data in the user form. For each repeat of the entry they have specified, the code I have add's the value '0.1' to a certain text field in the user form so each row entered has an ID which goes up in '0.1, 0.2, 0.3' etc. (start defined by user inputting data into form field, so they could start at 12.3 for example)

What i'm trying to figure out but can't get my head around is that when the ID text box decimal place value goes about x.6, I want it jump up to the next whole number so the sets of data inputted have an ID number which goes up in groups of six's. E.G.
3.5
3.6
4.1
4.2
4.3
4.4
4.5
4.6
5.1
5.2
5.3 etc....

Is there a VBA code I can add to my userform for this or a formatting rule anywhere that I just can't seem to find.

Any help would be muchly appreciated, if you have any questions or need more info, i'm happy to give more for you!

Keep well!

 

[MickG]

Try:-

Private Sub TextBox1_Change()
If IsNumeric(TextBox1.Value) Then
  If Val(TextBox1.Value) - Val(Int(TextBox1.Value)) > 0.7 Then
    TextBox1.Value = Int(TextBox1.Value) + 1.1
   End If
End If
End Sub

 

[07gashbridge]

Hi Mick,

Thank you for the code, works an absolute charm! Just one question if you can help me out, i'm trying to do a similar thing for another field which would contain text as well as numbers for essentially assigning unique circuits to the inputted data. Is there a way/code for the userform text box to be able to do the same as above but incorporate user inputted text as well?

...HP1.4
HP1.5
HP1.6
HP2.1
HP2.2
HP2.3
HP2.4
HP2.5
HP2.6
HP3.1
HP3.2 ...etc

And/Or below to go up to the next letter in the alphabet (if possible) when the code gets to 0.6 and moves onto the next number up, but instead the next letter in the alphabet?

...E4
E5
E6
F1
F2
F3
F4
F5
F6
G1
G2
G3... etc

If you understand where i'm coming from?

Or another way round would be for me to create two userform fields where one indicates a letter input for the user, example they type in 'E' and the next field is where they choose the starting circuit number in the 'E' group. There is a maximum of 6 circuits available in each lettered group so that when each entry gets to E.6 for example it goes up to F.1 then there's a code that could combing the two user forms to put the Letter and Number into the same cell on the spreadsheet?

Regards,
Gary.

 

[MickG]

Try first code, (TextBox2) Add Letters then double click for results

Private Sub TextBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Num As Double, Txt As String, Sp As Variant, n As Long
If TextBox2 <> "" Then
    For n = 1 To Len(TextBox2)
       If Not IsNumeric(Mid(TextBox2, n, 1)) And Not Mid(TextBox2, n, 1) = Chr(46) Then
            Txt = Txt & Mid(TextBox2, n, 1)
        End If
    Next n

   If TextBox2 = Txt Then
        TextBox2 = Txt & "1.0"
    Else
            Num = Right(TextBox2, Len(TextBox2) - Len(Txt))
        If Num - Int(Num) > 0.6 Then
            Num = Int(Num) + 1.1
        Else
            Num = Num + 0.1
        End If
        TextBox2 = Txt & Num
    End If
End If

End Sub

Try second code ("TextBox3") , Enter single letter then double click to obtain results :-

Private Sub TextBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Num As Double, Txt As String, n As Long
If TextBox2 <> "" Then
    For n = 1 To Len(TextBox3)
       If Not IsNumeric(Mid(TextBox3, n, 1)) And Not Mid(TextBox3, n, 1) = Chr(46) Then
            Txt = Txt & Mid(TextBox3, n, 1)
        End If
    Next n

   If TextBox3 = Txt Then
        TextBox3 = Txt & "1"
    Else
            Num = Right(TextBox3, Len(TextBox3) - Len(Txt))
            If Num > 5 Then
                    Num = 1
                  Txt = Chr(Asc(Txt) + 1)
           Else
                Num = Num + 1
           End If
        TextBox3 = Txt & Num
    End If
End If
End Sub