VB:. This works fine except when I try to eliminate ("Members") & use ("Sheet1") the code fails. I have triedActiveWorkbook.Sheets("Members").Tab.ColorIndex = 3If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:alsoActiveWorkbook.Sheet1.Tab.ColorIndex =3If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:& it fails. Is there a way to just use the sheet # & not the sheet Name to change the Tab Color? I have sheets in my workbook that the name changes under certain conditions & using the sheet name will ultimately make the code fail as well.ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex =3If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:thanksgetthesource () activesheet.Copy after:=Sheets(Sheets.Count)ActiveWorkbook.SaveAs Filename:="processing" activesheet.Name = "working" Workbooks.Open Filename:="Users:matthewchrycy:desktop:input.xlsx" Application.EnableEvents = False Workbooks("input.xlsx").Sheets("input").Copy after:=Workbooks("processing").Sheets("working") Application.EnableEvents = True Windows("processing").Activate ActiveWorkbook.Sheets("working").Select activesheet.Name = Format(Date, "mmm-dd-yyyy") ActiveWorkbook.SaveAs Filename:=Range("d3") End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer On Error GoTo ErrorHandler 'change the conditions below Select Case Target.Value Case "OFF" icolor = 35 Case "RTO" icolor = 8 Case "vacation" icolor = 8 Case "10-9 SBP" icolor = 38 Case Else End Select Target.Interior.ColorIndex = icolor ErrorHandler: On Error Resume Next End Sub
With ActiveWorkbook.Sheets("Sheet1").Tab .ColorIndex = xlAutomatic .TintAndShade = 0 End WithBut running that recorded marco (with no modification) causes the error: "Run-time error '9': Subscript out of range"
Private Sub CommandButton1_Click() Dim cSheet As Worksheet, cBook As Workbook, a As Integer Set cBook = ActiveWorkbook Set cSheet = ActiveSheet If txtSheetName = "" Then MsgBox "Error: Sheet name cannot be blank" ChgSheetName.Hide ChgSheetName.Show Else For a = 1 To cBook.Worksheets.Count If txtSheetName = cBook.Sheets(a).Name And a <> cSheet.Index Then MsgBox "Error: Name is already used in workbook" ChgSheetName.Hide ChgSheetName.Show End If Next a On Error GoTo ErrorHandler End If cSheet.Select cSheet.Name = txtSheetName If Bluebox = True Then With cSheet.Tab .Color = 15773696 .TintAndShade = 0 End With End If If Redbox = True Then With cSheet.Tab .Color = 255 .TintAndShade = 0 End With End If If Nonebox = True Then ' Nonebox is a checkbox in the userform to remove the tab color With cSheet.Tab .ColorIndex = xlAutomatic .TintAndShade = 0 End With End IfChgSheetName.Hide Exit Sub ErrorHandler: MsgBox "Error: " & Err.Description End SubI've reached a functional though non-ideal solution using