specialcells and toolbox button click event

  • Thread starter Thread starter Srinath
  • Start date Start date
S

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
 
Srinath,

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

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