CODEWITHSUNDEEP
excelvba
Lindy Newman
Lindy Newman

Reputation: 43

Button Visible property not working in Excel VBA

I'm working on a pretty simple spreadsheet with a Command Button (form control, not ActiveX) directly on the sheet. I want to make it so that when the button is clicked once, it is either hidden or locked so that it can't be clicked again without reopening the sheet.

Photo of button and name here.

I've tried btnNewMonth.Visible and btnNewMonth.Locked, and neither of these work. I've checked for typos about 500 times, and I can't find any. Is there something else I could be doing wrong?

I've pasted my whole code in here, but the offending line is the last one. It throws

Run-time Error 424: Object required.

Sub btnNewMonth_Click()

Dim lastmonth As Variant

Cells(1, 1) = DateAdd("m", 1, DateValue(Cells(1, 1)))
lastmonth = Range("D6:D9")
Range("C6:C9") = lastmonth
Range("D6:D9") = ""
Range("B17:G18") = ""
Range("B20:G21") = ""

btnNewMonth.Visible = False  'Run-time error 424: Object required

End Sub

Upvotes: 3

Views: 8929

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

See this answer if you need to locate ActiveX or Form Controls on a worksheet.

In this case, this should suffice:

ActiveSheet.Shapes.Item("btnNewMonth").Visible = False

I'd warmly recommend working against a specific Worksheet object instead of implicitly or explicitly working off ActiveSheet though. For example if you change the (Name) property of your worksheet to, say, SummarySheet, then you can do this:

SummarySheet.Shapes.Item("btnNewMonth").Visible = False

Upvotes: 3

Related Questions