ShawnSPS
Board Regular
- Joined
- Mar 15, 2023
- Messages
- 61
- Office Version
- 2003 or older
- Platform
- Windows
I am currently working on a project. I have three Marco’s that I would like to combine into one action when a button is pressed.
The follow are the Marcos that I currently created but conceptually and literally being a novice in vba programming I having a hard time working through the changes I want and need in with the vba I had have written. This is a bit of a project but maybe someone can help .. see below for the Marco’s and the Spreadsheet
macros"
Sub CycleMonthThruWbs()
Dim ws As Worksheet
Dim MonthName As String
Dim Mn As String
Mn = Format(Date, "mmmm")
For Each ws In ThisWorkbook.Sheets
' Debug.Print ws.Name
ws.Unprotect ("Pila1DA.#")
ws.Activate
Range("A1").Value = Mn
ws.Protect ("Pila1DA.#")
Next ws
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
End Sub
__________________________________________________________________________________
Sub ClearContents()
Dim iterator As Long
Application.DisplayAlerts = False
For iterator = ThisWorkbook.Worksheets.Count To 1 Step -1
With ThisWorkbook.Worksheets(iterator)
'If .CodeName <> "Znotes,ZShortcuts" Then Range("D3:AH31,D34:AH41").ClearContents
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents
End With
Next iterator
Application.DisplayAlerts = True
End Sub
______________________________________________________________________________________
Sub sbSaveExcelDialog()
Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub
thanks for all your help
- Action is to have the user enter a save as file name, which would save the current workbook and Data to their documents on the computer or laptop.
- Second it would warn them that the workbook will be renamed & updated to the new month entered and data cleared. I them to be to select the month that workbook and sheets are being created for the month and the month place in A1 on all sheets and the data would clear out the all the sheet from the previous Month In the range I provided.
- Third I want it again the user enters as save as new file name, which would save new workbook and Data to their documents on the computer or laptop. So when they are working or adding data all the have do press save
The follow are the Marcos that I currently created but conceptually and literally being a novice in vba programming I having a hard time working through the changes I want and need in with the vba I had have written. This is a bit of a project but maybe someone can help .. see below for the Marco’s and the Spreadsheet
macros"
Sub CycleMonthThruWbs()
Dim ws As Worksheet
Dim MonthName As String
Dim Mn As String
Mn = Format(Date, "mmmm")
For Each ws In ThisWorkbook.Sheets
' Debug.Print ws.Name
ws.Unprotect ("Pila1DA.#")
ws.Activate
Range("A1").Value = Mn
ws.Protect ("Pila1DA.#")
Next ws
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
End Sub
__________________________________________________________________________________
Sub ClearContents()
Dim iterator As Long
Application.DisplayAlerts = False
For iterator = ThisWorkbook.Worksheets.Count To 1 Step -1
With ThisWorkbook.Worksheets(iterator)
'If .CodeName <> "Znotes,ZShortcuts" Then Range("D3:AH31,D34:AH41").ClearContents
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents
End With
Next iterator
Application.DisplayAlerts = True
End Sub
______________________________________________________________________________________
Sub sbSaveExcelDialog()
Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub
thanks for all your help