WildBurrow
New Member
- Joined
- Apr 5, 2021
- Messages
- 38
- Office Version
- 365
- Platform
- Windows
I'm stepping off the porch to play with the big dogs; I'm posting my first question and providing a sample of my code.
I am using input message boxes to obtain info that will 1) populate cells on the worksheet, and 2) be used in the naming structure for the worksheet. The issue is that I'd like to further tweak how the data is trimmed. There are four cells that are used in the sheet name:
Currently, I have code that will trim the data to 1) accommodate their maximum character lengths and 2) conform to the 31 character limit for worksheet naming structure (code below).
So for instance:
I have a site visit date of 9/23/21 and the address was 12345 Birkshire Wilmont, Unit 1234.
My sheet name would be:
12345_BirkshireWi_1234_09.23.21
Unfortunately, if an address number only has 2 characters (e.g. house number 22), and no unit number, I'm not utilizing my full 31 character limit and I can't shift those empty spaces to street name, which would be ideal.
Same site visit date but the address is 22 Birkshire Wilmont with no unit number
My sheet name would be:
22_BirkshireWil_09.23.21
Is there a way to trim to a maximum number of characters for each cell but then allow any "extra" spaces to be used for the street name?
My full code is below (hot mess alert!)
I am using input message boxes to obtain info that will 1) populate cells on the worksheet, and 2) be used in the naming structure for the worksheet. The issue is that I'd like to further tweak how the data is trimmed. There are four cells that are used in the sheet name:
| Cell Label | Parameters | Cell Maximum Characters |
| ||
|---|---|---|---|---|---|
| Site Visit Date | Always used | 8 | mm.dd.yy | ||
| Address Number | Always used , may have less than 5 characters | 5 | alpha-numeric | ||
| Street Name | Always used, may have less than 19 characters | 19 | alpha-numeric | ||
| Unit Number | Infrequently used but needed is applicable | 5 | alpha-numeric |
Currently, I have code that will trim the data to 1) accommodate their maximum character lengths and 2) conform to the 31 character limit for worksheet naming structure (code below).
VBA Code:
ShNmExt = Trim(Left(RAddNo, 5) & "_" & Left(RStreetNm, 11) & "_" & Left(RUnit, 4))So for instance:
I have a site visit date of 9/23/21 and the address was 12345 Birkshire Wilmont, Unit 1234.
My sheet name would be:
12345_BirkshireWi_1234_09.23.21
Unfortunately, if an address number only has 2 characters (e.g. house number 22), and no unit number, I'm not utilizing my full 31 character limit and I can't shift those empty spaces to street name, which would be ideal.
Same site visit date but the address is 22 Birkshire Wilmont with no unit number
My sheet name would be:
22_BirkshireWil_09.23.21
Is there a way to trim to a maximum number of characters for each cell but then allow any "extra" spaces to be used for the street name?
My full code is below (hot mess alert!)
VBA Code:
Private Sub CommandButton10_Click()
'Create Special Investigation Report
Dim iWindowState As Integer
With Application
.ScreenUpdating = False
.DisplayAlerts = False
iWindowState = .WindowState
End With
'Site Visit Information
Dim NewName As String, NewDate As String, msg As String, Ans As Long, NewSite As String, AddNo As String, StreetNm As String, Unit As String, RAddNo As String, RStreetNm As String, RUnit As String, ShNmExt As String
'Enter Date for name of sheet (NewDate)
NewDate = InputBox("Enter Site Visit Date" _
& vbNewLine & vbNewLine & "Use forward slash in date: mm/dd/yy", "Date Entry", "mm/dd/yy")
'Set format for Site Visit Date (NewSite)
NewSite = Format(NewDate, "mm/dd/yy"
'If nothing entered into Date Input Box, exit sub
If NewDate = "" Then Exit Sub
'Enter Address Number for name of sheet (Add)
AddNo = InputBox(" " & vbNewLine & vbNewLine & "Enter Address Number", "Affected Party Address Number", 0)
If AddNo = "" Then Exit Sub
'Enter Street Name for name of sheet (StreetNm)
StreetNm = InputBox("Enter Street Name Only - No Suffixes" & vbNewLine & vbNewLine & "NOTE: For State Routes, County or Township Roads/Highways enter jurisdictional level and the numeric designator (e.g. County 122)." _
& vbNewLine & vbNewLine & " - Do not add the suffix (e.g. Route, Road, Highway)" & vbNewLine, "Affected Party Street Name", "County 122")
If StreetNm = "" Then Exit Sub
'Enter Unit Number for name of sheet (Unit)
Unit = InputBox(" " & vbNewLine & vbNewLine & "Enter Unit Number, if applicable", "Affected Party Unit Number")
'Rename Worksheet
'If date then format name using decimal point (not slashes) - if not a date, the provide error message and exit sub
If IsDate(NewDate) Then
NewName = Format(CDate(NewDate), "mm.dd.yy")
Else
MsgBox "Please enter a valid date format"
Exit Sub
End If
'If sheet exists provide error message
If SheetExists(NewName) Then
msg = "A sheet with the name " & NewName & " aleady exists."
Ans = MsgBox(msg, vbOK)
Exit Sub
End If
'Copy Master
Sheets("Master").Copy after:=Sheets("Dashboard")
'Trims cell values used in new sheet name (ShNmExt)
RAddNo = Replace(AddNo, " ", "")
RStreetNm = Replace(StreetNm, " ", "")
RUnit = Replace(Unit, " ", "")
ShNmExt = Trim(Left(RAddNo, 5) & "_" & Left(RStreetNm, 11) & "_" & Left(RUnit, 4))
'Activate / Active Sheet
'Provide Name for New Sheet
ActiveSheet.name = ShNmExt & "_" & NewName
'Populate report data
.Range("U25").value = NewDate
.Range("AA51").value = "Select..."
.Range("B72").value = "Weather at time of Site Visit"
.Range("B127").value = "Site Description"
.Range("N127").value = ""
'Populate fields used in Sheet Name
.Range("Q51").value = AddNo
.Range("U51").value = StreetNm
.Range("AC51").value = Unit
ActiveSheet.Protect ("Fieldops"), UserInterFaceOnly:=True
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.WindowState = iWindowState
End With
End Sub
