How to Insert Dash Between Letters and Decimal Number?

[ItsDavid]

Hello all,

I am brand new to the forum and this is my first post.

I am very new to VBA and by far an expert and was hoping to find some help here.

I currently have a worksheet that gets used by many different users so trying to make sure that everyone enters the data into cells the same is no easy task so I would like to use VBA Worksheet_Change to auto format these cells but with all attempts I have come close, however, nothing as been to perfection. I have searched Google, Bing and found many forum posts from here in the search results but to no avail. With that said let me explain what I have going on.

In the worksheet there are 13 different codes in a named range that start with letters ranging from 2 letters to 4 letters, (ab, abc, abcd) I would like to have a dash ("-") automatically inserted after the letter codes when the user hits enter to avoid relying on each user typing it manually.

The dash is followed by a decimal number that the user manually enters such as .25, .5, .75, 1.0, 1.25, 1.5, 1.75 etc.

If it's possible I would also like to allow the user to type the entry with or without spaces and have it auto format such as (abcd1.75) becomes (abcd - 1.75) when the user hits enter

I would also like it so it will follow the automatic format pattern if the user enters (abc 1.75) it becomes (abc - 1.75) when the user hits enter.

I would also like to restrict these cells to only accept values that begin with any one of the 13 letter codes and follow the pattern outlined, ab - decimal number, abc - decimal number, abcd - decimal number.


The cells the data is entered into by the user is in a named range.

I am hoping this is something that can be done with VBA or Regex. I did use the Autoformat Proofing features of excel which worked great on my PC only and did not work for anyone else.


Thank you in advance.

 

[Zot]

What is the 13 letter codes? Please explain more on the restriction. What is the named range region? Get lost here.

 

[Peter_SSs]

Welcome to the MrExcel board!

As well as telling us what the codes are ..
- are the 13 codes stored anywhere in the workbook?
- does it matter if the letter codes are upper case or lower case?

 

[ItsDavid]

What is the 13 letter codes? Please explain more on the restriction. What is the named range region? Get lost here.

My apologies, I should have used a better choice of words.

there are 13 options of letter codes starting with the minimum letter count being only 2 letters and the maximum amount of letters being 4

al, fmla, hl, vbid, pl, ss, sw, ta, tm, tua, ua, lt, wcl

these codes are positioned at the top of the sheet in rows 2 and 3 and the named range is tuCodes.

Welcome to the MrExcel board!

As well as telling us what the codes are ..
- are the 13 codes stored anywhere in the workbook?
- does it matter if the letter codes are upper case or lower case?

Please see reply above your quote.

as it is now, anything typed in the named range CalDay is automatically converted to uppercase.

 

[Peter_SSs]

Thanks for the additional information. I forgot to ask
- exactly what you want done if an invalid value is entered
- if it is possible that multiple entries could be made at once (eg by copy/paste or Ctrl+Enter confirmation)

This code does allow for multiple entries at once and for any invalid entries it just colours them yellow.
I have included the capitalisation of any valid entries in this code.

Test with a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object
  Dim Changed As Range, c As Range
  Dim s As String
  
  Set Changed = Intersect(Target, Range("CalDay"))
  If Not Changed Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.ignorecase = True
    For Each c In Range("tuCodes")
      If Len(c.Value) > 0 Then s = s & "|" & c.Value
    Next c
    RX.Pattern = "(^" & Mid(s, 2) & ")(\d*\.?\d*$)"
    Application.EnableEvents = False
    For Each c In Changed
      s = Replace(Replace(c.Value, " ", ""), "-", "")
      If RX.Test(s) Then
        c.Value = UCase(RX.Replace(s, "$1 - $2"))
      Else
        c.Interior.Color = vbYellow
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[ItsDavid]

Thanks for the additional information. I forgot to ask
- exactly what you want done if an invalid value is entered
- if it is possible that multiple entries could be made at once (eg by copy/paste or Ctrl+Enter confirmation)

This code does allow for multiple entries at once and for any invalid entries it just colours them yellow.
I have included the capitalisation of any valid entries in this code.

Test with a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object
  Dim Changed As Range, c As Range
  Dim s As String
 
  Set Changed = Intersect(Target, Range("CalDay"))
  If Not Changed Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.ignorecase = True
    For Each c In Range("tuCodes")
      If Len(c.Value) > 0 Then s = s & "|" & c.Value
    Next c
    RX.Pattern = "(^" & Mid(s, 2) & ")(\d*\.?\d*$)"
    Application.EnableEvents = False
    For Each c In Changed
      s = Replace(Replace(c.Value, " ", ""), "-", "")
      If RX.Test(s) Then
        c.Value = UCase(RX.Replace(s, "$1 - $2"))
      Else
        c.Interior.Color = vbYellow
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

I have not tested this yet, however I will be testing it shortly.

to answer you questions.

the color shading is not needed because I already have conditional formatting in place to shade specific colors depending on “text begins with”

if a user enters a value not allowed it would be nice to have an error message pop up with a massage “Entry invalid. You may only enter values from the list of available codes” when the user clicks the ok button it clears their invalid entry to allow them to enter an accepted value.

only one valid entry should be allowed.

thank you so much for your time and effort helping me with this. I will report back once I have a chance to test.

 

[Peter_SSs]

OK, if users will only ever be entering in one cell at a time in CalDay then try this version.
I have also tweaked the pattern a bit as it was allowing some invalid entries I discovered. Hopefully this is more robust. ?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object
  Dim Changed As Range, c As Range
  Dim s As String
 
  Set Changed = Intersect(Target, Range("CalDay"))
  If Not Changed Is Nothing Then
    If Changed.Count = 1 Then
      If Len(Changed.Value) > 0 Then
        Set RX = CreateObject("VBScript.RegExp")
        RX.IgnoreCase = True
        For Each c In Range("tuCodes")
          If Len(c.Value) > 0 Then s = s & "|" & c.Value
        Next c
        RX.Pattern = "^(" & Mid(s, 2) & ")((\d+\.?\d*)|(\d*\.?\d+))$"
        Application.EnableEvents = False
        s = Replace(Replace(Changed.Value, " ", ""), "-", "")
        If RX.Test(s) Then
          Changed.Value = UCase(RX.Replace(s, "$1 - $2"))
        Else
          Changed.Select
          MsgBox "Entry invalid. You may only enter values from the list of available codes"
          Changed.ClearContents
        End If
        Application.EnableEvents = True
      End If
    End If
  End If
End Sub

 

[ItsDavid]

OK, if users will only ever be entering in one cell at a time in CalDay then try this version.
I have also tweaked the pattern a bit as it was allowing some invalid entries I discovered. Hopefully this is more robust. ?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object
  Dim Changed As Range, c As Range
  Dim s As String

  Set Changed = Intersect(Target, Range("CalDay"))
  If Not Changed Is Nothing Then
    If Changed.Count = 1 Then
      If Len(Changed.Value) > 0 Then
        Set RX = CreateObject("VBScript.RegExp")
        RX.IgnoreCase = True
        For Each c In Range("tuCodes")
          If Len(c.Value) > 0 Then s = s & "|" & c.Value
        Next c
        RX.Pattern = "^(" & Mid(s, 2) & ")((\d+\.?\d*)|(\d*\.?\d+))$"
        Application.EnableEvents = False
        s = Replace(Replace(Changed.Value, " ", ""), "-", "")
        If RX.Test(s) Then
          Changed.Value = UCase(RX.Replace(s, "$1 - $2"))
        Else
          Changed.Select
          MsgBox "Entry invalid. You may only enter values from the list of available codes"
          Changed.ClearContents
        End If
        Application.EnableEvents = True
      End If
    End If
  End If
End Sub

You are a ROCK STAR!!! So far everything is working as it should. Thank you so much. I have been playing around with other languages such as PHP, JavaScript etc. Only recently I have been getting my hands dirty with VBA and have spent that last 4 weeks or so trying to get this to come together. I did manage to get close, however, I did not nail it the way you did.

I do have one more question.

During testing I noticed that if I enter any one of the provided codes like "code80" or "code 80" it does not add the decimal point between the 8 and 0 to make it (8.0). Is there a way to modify this so that it automatically inserts the period "." if the user forgets to manually type it in?

I am thinking this may not be possible because if a user were to type for instance "code75" or "code 75" which should actually be 3/4 of an hour "code - .75" I would imagine there is no way to depict that automatically and we just have to rely on users not forgetting to enter that period where it belongs. because there is also the chance the entry could be "code - 7.5"?

Would my assumption be correct on that?

 

[Peter_SSs]

I am thinking this may not be possible because if a user were to type for instance "code75" or "code 75" which should actually be 3/4 of an hour "code - .75" I would imagine there is no way to depict that automatically and we just have to rely on users not forgetting to enter that period where it belongs. because there is also the chance the entry could be "code - 7.5"?

There was no mention in your original question that the number part was "hours" or that there was any limit on the "decimal number" entered. Therefore my code allows
"UA - 12345678.213254", "HL - .000008" etc.

If both .75 and 7.5 are allowed then you are right, there is no way to automatically insert the decimal point. It would be the same for "code80" anyway wouldn't it (8.0 or .80)?

What are the limits on the decimal number?
- Can it be >= 10? (eg 11.5)
- Can there be more than 2 decimal places? (eg 5.0833 being 5 hours and 5 minutes approximately)

 

[ItsDavid]

There was no mention in your original question that the number part was "hours" or that there was any limit on the "decimal number" entered. Therefore my code allows
"UA - 12345678.213254", "HL - .000008" etc.

If both .75 and 7.5 are allowed then you are right, there is no way to automatically insert the decimal point. It would be the same for "code80" anyway wouldn't it (8.0 or .80)?

What are the limits on the decimal number?
- Can it be >= 10? (eg 11.5)
- Can there be more than 2 decimal places? (eg 5.0833 being 5 hours and 5 minutes approximately)

You are correct, it totally skipped my mind to point out these specific details. To be completely honest it slipped my mind up until the point I was testing what you provided and missed entering the “.” between the 8 and zero and ended up with “code 80”.

the time can be added in whole hours such as 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0 with the Max whole hour being 8.0. The hours can also be in quarter hour increments such as .25, .5, .75, 1.25, 1.5, 1.75, 2.0 all the way to a max of 8.0.

no single entry should exceed 8.0

hopefully that explains it more clearer.

i would like to once again express my biggest thank you for the help you have provided. This is already a huge step forward to a breath of fresh air.