ItsDavid
New Member
- Joined
- Mar 24, 2021
- Messages
- 10
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
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.
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.