This is the code I use to trigger auto-generation and validation routines:
Private Sub Worksheet_Change(ByVal Target As Range)
'// Cloud Only Routines
Call setResourceGroupName(Target)
Call setFinancialCodeValue(Target)
Call setVhdImageName(Target)
Call setDataDisk1Defaults(Target)
Call setDataDiskDefaults(Target)
Call setCloudIP(Target)
'// Cloud and On-Prem Routines
Call setDeviceID(Target, C_SHEETNAME_CLOUD)
Call setServerName(Target, C_SHEETNAME_CLOUD)
Call setServerSize(Target, C_SHEETNAME_CLOUD)
Call validateServerName(Target, C_SHEETNAME_CLOUD)
Call validateDeviceNSG(Target, C_SHEETNAME_CLOUD)
Call validateAppSectyGrp(Target)
Call validateAvailSetName(Target)
Call validateDataDiskSet(Target)
Call validateStorageAccount(Target)
Call validateResourceGrp(Target)
Call validateCloudIP(Target)
'// Set applicable column values to lowercase
Call setTextToLowerCase(Target, C_SHEETNAME_CLOUD)
End Sub
This is the code I use to set the server name. This code has been working forever , until this past week.
Public Sub setServerName( _
ByVal Target As Range, _
strSheetName As String _
)
'Name: <site code><location><function code><unique identifier>-<sub-site identifier>
'# char 4 2 3 3 1
On Error GoTo CleanExit
If Target.Count > 1 Then GoTo CleanExit
If Target.row = 1 Then GoTo CleanExit
Dim lgTgtCol As Long
lgTgtCol = convertLetterToLong(C_COL_LTR_SERVER_TYPE)
If Not Target.Column = lgTgtCol Then GoTo CleanExit
If strSheetName = C_SHEETNAME_ONPREM And Cells(Target.row, lgTgtCol) = C_LEGACY_SVR_ONPREM Then GoTo CleanExit
Excel.Application.enableEvents = False
If strSheetName = C_SHEETNAME_CLOUD And Target = C_LEGACY_SVR_ONPREM Then
Dim strMsg As String
strMsg = "This Server Type can only be used on the On-Prem worksheet."
Dim r As Integer
r = MsgBox(Prompt:=strMsg, Buttons:=vbOKCancel + vbCritical + vbMsgBoxSetForeground, Title:="Error Manager")
Cells(Target.row, Target.Column) = ""
GoTo CleanExit
End If
Dim lgDestCol1 As Long
lgDestCol1 = convertLetterToLong(C_COL_LTR_NAME)
'// If Server Type is blank,
'// Erase Server Name
If Target.Value = "" Then
'// Blank out Name
Cells(Target.row, lgDestCol1) = ""
'// If not blank, set Server Name
Else
'// Get Vnet name
Dim strVnetName As String
strVnetName = Cells(Target.row, convertLetterToLong(C_COL_LTR_ENV))
'Get Site Code
'<site code>
Dim strSiteCode As String
strSiteCode = getDefaultSitecode( _
ActiveSheet.Name, _
Target.row)
'Get Location
'<location>
Dim strLocation As String
strLocation = Mid$(strVnetName, I_LEN_SITE_CODE + 1, nameVal.cStateCode)
If strSheetName = C_SHEETNAME_CLOUD Then
Dim strVnetCode As String
strVnetCode = getVnetCode( _
strVnetName)
Dim strSubnetCode As String
strSubnetCode = getSubnetCode( _
Cells(Target.row, convertLetterToLong(C_COL_LTR_SUBNET_OR_ZONE)), _
strVnetCode)
strSiteCode = strSiteCode & strLocation
End If
''''''''''''''''''''''''''''''''
'// Set Name Column
''''''''''''''''''''''''''''''''
'// Get Function Code
Dim existingRoleCode As String
existingRoleCode = getFunctionCode(Target.Offset(0, 1).Value)
'<function code>
Dim strFunctionCode As String
strFunctionCode = xlvLookup( _
C_SHEETNAME_LISTS, _
C_RANGENAME_LISTS_SERVER_ROLE, _
Target.Value, _
0, _
1)
'<sub-site identifier>
Dim strLkupValue As String
strLkupValue = IIf(strSheetName = C_SHEETNAME_CLOUD, strSubnetCode, strVnetName)
Dim subsiteID As String
subsiteID = getSubsiteCode( _
ActiveSheet.Name, _
strLkupValue)
'// If Server Role Name Changed, update Name
'// Set Server Name
Cells(Target.row, lgDestCol1) = _
strSiteCode & _
strFunctionCode & _
C_SERIAL_NUM_PATTERN & _
subsiteID
'// If server type "other" flag set server type background color
If Cells(Target.row, Target.Column) = "Other Server" Then
Call setTextRed(Target.row, Target.Column)
Else
Call setTextNormal(Target.row, Target.Column)
End If
'// Set Background color to red if serial num = ### (the auto-generation default)
'// Otherwise remove background color
Dim vTargetCell As String
vTargetCell = C_COL_LTR_NAME & Trim(Str(Target.row))
Dim vTarget As Excel.Range
Set vTarget = Range(vTargetCell)
If InStr(vTarget.Value, C_SERIAL_NUM_PATTERN) > 0 Then
Call setTextRed(Target.row, Target.Column + 1)
Else
Call setTextNormal(Target.row, Target.Column + 1)
End If
End If
CleanExit:
Excel.Application.enableEvents = True
On Error GoTo 0
End Sub