Simulating the Format Painter, Selection.Select, and taking focus

[David McRitchie]

I've often seen postings relating to Buttons concerning Taking Focus
but now in trying to find out why I need Selection.Select to get
a macro to work. I ran across several postings mentioning
"Taking Focus" while searching the Google groups archives on
"selection.select", so I guess I need a clear understanding
of what this really means.

I created a macro to simulate the Format Painter which can resize
it's output to match the selection of the destination rather than the
dimension of the source. The resizing only occurs by including
selection.select
otherwise you must choose 1 cell, or a matching dimension for the
destination. More complete description and examples in
http://www.mvps.org/dmcritchie/excel/paste.htm#painter

Sub PasteSpecialFormats()
'David McRitchie, paste.htm, programming, 2003-07-08
Selection.Select 'makes it work like the Format Painter
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Can someone tell me what taking focus means here. Without the
selection.select what has the focus.

 

[Dave Peterson]

I tried your code in xl2002. I assigned it to a button from the forms toolbar.

I commented out "selection.select". It worked exactly the same way with that
line in as it did with that line commented out.

xl97 had a bug with controls from the controltoolbox toolbar used on a
worksheet. One remedy was to set the control's .takefocusonclick property to
false. But not all controls have that property. A common solution was to add:

activecell.activate
or
selection.select

to the top of your code.

(It was fixed in xl2k.)

I added a shortcut to the macro and it worked ok, too.

I don't think I've ever seen the takefocusonclick ever referred to except for
this control toolbox stuff.

I've often seen postings relating to Buttons concerning Taking Focus
but now in trying to find out why I need Selection.Select to get
a macro to work. I ran across several postings mentioning
"Taking Focus" while searching the Google groups archives on
"selection.select", so I guess I need a clear understanding
of what this really means.

I created a macro to simulate the Format Painter which can resize
it's output to match the selection of the destination rather than the
dimension of the source. The resizing only occurs by including
selection.select
otherwise you must choose 1 cell, or a matching dimension for the
destination. More complete description and examples in
http://www.mvps.org/dmcritchie/excel/paste.htm#painter

Sub PasteSpecialFormats()
'David McRitchie, paste.htm, programming, 2003-07-08
Selection.Select 'makes it work like the Format Painter
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Can someone tell me what taking focus means here. Without the
selection.select what has the focus.
---
HTH, (using Excel 2000 and the OS is Windows 2000)
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

Hi Dave,
Since you said the bug was fixed in Excel 2000, I retested this
morning and it worked without selection.select, it later failed/worked
again, so don't know where the inconsistencies were. I had been
testing both from a toolbar button (for testing) and from Alt+F8
macro dialog, and my assigned shortcut Ctrl+Shift+P.

Even if it worked consistently in Excel 2000, from your comments
without the Selection.Select, it would be needed in Excel 97
at least under some circumstance. Though I would never assign
a control on the spreadsheet for it as opposed to the toolbar..

The main advantage and why I created the macro was to be able
to assign a shortcut key; otherwise, the format painter does the
same. Some advantage in being able to use Ctrl+C rather than
extra steps of double-clicking on Format Painter first.

Also discovered the macro and therefore also the format painter
can be used to clear out Conditional Formatting without
knowing the exact original ranges of C.F., something that had
pestered me before. In fact if you have borders and want to
clear out all other formatting that is easily done, having an
advantage over Edit, Clear, Formats which can also clear
out all formatting including C.F..

I changed comments on my
http://www.mvps.org/dmcritchie/excel/paste.htm
page and changed the code as follows.

Sub PasteSpecialFormats() 'assign macro to Ctrl+SHIFT+P
If Application.CutCopyMode = False Then
MsgBox "Clipboard is empty or does not have cell formats"
Exit Sub
End If
Selection.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

This may be one of the very few questions I asked here as an original
question -- does everyone get a DEVX email when they post to these
newsgroups as an original posting? As Dave Bradon would say sheesh.

 

[Dave Peterson]

I posted a question and got an email from devx asking me if I wanted to receive
notifications of responses. I answered no. (I avoided the sheesh factor <bg>.)