MrExcel.com - Your One Stop for Excel Tips & Solutions

Robb or anyone, macro button question

Posted by RoB on August 22, 2001 1:28 PM

Ok, I have code from you guys which works great. Its a macro that saves the file as a specific name. I also put in a piece of code to hide the button after the macro was executed (as i only wanted the button pressed once). This worked good, but I found that when i close the file and open it again, the button re-appears. Is there a way around this?
Thanks, heres the code of the whole page:

Sub SaveMacro()

Mo = Month(Now())

If Mo = 0 Then
Mo = 12
Yr = Format(Now(), "YY")
YRL = Format(Now(), "YYYY")
Else
Yr = Format(Now(), "YY")
YRL = Format(Now(), "YYYY")
End If

Select Case Mo
Case 1
MM = "01"
Case 2
MM = "02"
Case 3
MM = "03"
Case 4
MM = "04"
Case 5
MM = "05"
Case 6
MM = "06"
Case 7
MM = "07"
Case 8
MM = "08"
Case 9
MM = "09"
Case 10
MM = "10"
Case 11
MM = "11"
Case 12
MM = "12"
End Select
Borrower = Range("AL8")
LoanOfficer = Range("F4")
MyName = "S:\FileServer\Excel\Save FILES In Here\YEAR " & YRL & "\" & MM & "-" & Yr & "\" & (LoanOfficer) & "\" & (Borrower) & ".xls"

MsgBox ("File Saved As: " & MyName)
Application.ActiveWorkbook.SaveCopyAs (MyName)
Sheets("Application").PrintOut
Worksheets("Prelim Info").Shapes("SAVEBUTTON").Visible = False

End Sub


Posted by Robb on August 22, 2001 2:33 PM

Rob

Try moving the Visible statement to before the Save statement ie:

MsgBox ("File Saved As: " & MyName)
Worksheets("Prelim Info").Shapes("SAVEBUTTON").Visible = False
Application.ActiveWorkbook.SaveCopyAs (MyName)
Sheets("Application").PrintOut

Any help?

Regards

Posted by Barrett South London on August 22, 2001 2:34 PM

Remove Macro button ofter use ONCE!

Firstly your code is good ill have a good play many thanks, i save unique names with time as always different no matter if two the same names, worth rem that one xyz date / time

Now i kill any old bits ive finished with stops getting in the way, DELETE it not hide in false command, record the code if not sure and add in!

That will do it, if needs be add button, also i assign yellow smile face button on tool bar (out the way then)Dont want no smile face with pink hair like i have thou: :-)

Good luck

Posted by Barrett South London on August 22, 2001 2:36 PM

Re: Remove Macro button ofter use ONCE!

Save after delete not before

Forgot that bit, my fault

Posted by RoB on August 22, 2001 3:09 PM

Wow...Duh for me again...so obvious, thanks :)

: Ok, I have code from you guys which works great. Its a macro that saves the file as a specific name. I also put in a piece of code to hide the button after the macro was executed (as i only wanted the button pressed once). This worked good, but I found that when i close the file and open it again, the button re-appears. Is there a way around this? : Thanks, heres the code of the whole page: : Mo = 12 : Yr = Format(Now(), "YY") : YRL = Format(Now(), "YYYY") : Else : Yr = Format(Now(), "YY") : YRL = Format(Now(), "YYYY") : End If : Case 1 : MM = "01" : Case 2 : MM = "02" : Case 3 : MM = "03" : Case 4 : MM = "04" : Case 5 : MM = "05" : Case 6 : MM = "06" : Case 7 : MM = "07" : Case 8 : MM = "08" : Case 9 : MM = "09" : Case 10 : MM = "10" : Case 11 : MM = "11" : Case 12 : MM = "12" : End Select : Borrower = Range("AL8") : LoanOfficer = Range("F4") : MyName = "S:\FileServer\Excel\Save FILES In Here\YEAR " & YRL & "\" & MM & "-" & Yr & "\" & (LoanOfficer) & "\" & (Borrower) & ".xls" : Application.ActiveWorkbook.SaveCopyAs (MyName) : Sheets("Application").PrintOut : Worksheets("Prelim Info").Shapes("SAVEBUTTON").Visible = False : End Sub

Posted by RoB on August 22, 2001 4:13 PM

Dont thank me, thank the people on this board

Forgot that bit, my fault