Hiding Buttons in Macro


Posted by RoB on August 17, 2001 2:21 PM

I'm still having the error "Object does not support this property or method" when I execute my code to hide a button. The sheet the button is on is called "Application" and I named the button by using the name box "SAVEBUTTON". Here is the code Robb gave me, but I cant seem to get it to work. (By the way Robb, I tried using Me, it said "Invalid use of Me keywork"...what exactly is "Me"?)

here is the whole page of code I use. The applicable part is in bold:

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("E10")
MyName = "S:\FileServer\Excel\Save FILES In Here\YEAR " & YRL & "\" & MM & "-" & Yr & "\" & (Borrower) & ".xls"

MsgBox ("File Saved As: " & MyName)
'Application.ActiveWorkbook.SaveCopyAs (MyName)

Worksheets("Application").SAVEBUTTON.Enable = False
Worksheets("Application").SAVEBUTTON.Visible = False

End Sub

Posted by cpod on August 17, 2001 2:55 PM

I think the problem is your choice of sheet name. Application is a reserved word in Visual Basic. Try changing that and see what happens.

Posted by cpod on August 17, 2001 3:10 PM

Re: Never Mind

Never mind. I tried it and it works with a sheet name of "Application".

Posted by RoB on August 17, 2001 3:24 PM

It works for you? what am i doing wrong???

Posted by cpod on August 17, 2001 3:40 PM

Re: It works for you? what am i doing wrong???

I just noticed that you have "Enable" in your code. It should be "Enabled". Maybe that's it?

Posted by Robb on August 17, 2001 3:44 PM

Rob
If that is the exact code you are using, one small amendment:

Worksheets("Application").SAVEBUTTON.Enabled = False

Change Enable to Enabled

Does that work?

Regards

Posted by Rob on August 17, 2001 4:11 PM

Thats not it :(

I took that line out and just left the line:

Sub Button()

Worksheets("Sheet1").SAVEBUTTON.Visible = False

End Sub

I made a new sheet and named it "Sheet1" and named the button "SAVEBUTTON" (to eliminate the possibility of other code causing errors). Even with this simple code, i get the same error :( HELP!

Thanks

Posted by Robb on August 17, 2001 4:23 PM

Re: Thats not it :(

Rob

How are you renaming the Button?

If you want to e-mail me the sheet with the button and code, I'll have a look.

You have the address.

Regards

Rob

Posted by Ivan F Moala on August 17, 2001 4:31 PM

Re: Thats not it :(

Your command button that is on the worksheet
is part of the sheets shapes collection.

Use this syntax instead.

Worksheets("Sheet1").Shapes("CommandButton1").Visible = False

Where "Commandbutton1" = the shapes name


Ivan



Posted by RoB on August 17, 2001 4:47 PM

That was it! Thanks