specialcells and toolbox button click event

[Srinath]

Hello,

I have problems with usage of SpecialCells . What I have is as below
1. 2 Sheets named "Page1" and "Page2"
2. "Page2" contains a "control Toolbox" Button
3. I have the On_Click event macro. This macro does the following
a. On Click the sheet "Page1" is unprotected via code
b. Use the specialcells as Set UsedRangeCol =
Worksheets("Page1").Range("A5:A255").SpecialCells(xlCellTypeConstants)

I encounter a Problem here which is runtime error 1004

I found out that when the Button on "page2" has focus the sheet on which the
button is present has some Toolbars Greyed out (as if in protection). If i
press the button and then when i get the Error I select a Cell (any cell) in
"Page2" and do a single step the error is not present and everything works
fine . However if the Button has focus and i get an error and i do a single
step then the error is still present.

Why so? What should i do to circumvent this problem

Regards,
SSR

 

[Bernie Deitrick]

Srinath,

Right click on your button, select Properties, then change the TakeFocusOnClick property to False.

HTH,
Bernie
Excel MVP

 

[Tom Ogilvy]

Are you using Excel 97? If so,
in the properties of the button, change the TakefocusOnClick Property of the
button to false.