Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating the Format Painter, Selection.Select, and taking focus
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating the Format Painter, Selection.Select, and taking focus
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. David McRitchie wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating the Format Painter, Selection.Select, and taking focus
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. --- HTH, (My Excel version is 2000, running under 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 "Dave Peterson" wrote ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating the Format Painter, Selection.Select, and taking focus
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.) David McRitchie wrote: <<snipped 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to show cell selection when excel not in focus + other questio | Setting up and Configuration of Excel | |||
Format painter | Excel Worksheet Functions | |||
copy conditional format without using format painter | Excel Worksheet Functions | |||
Format Painter | Excel Discussion (Misc queries) | |||
how can I use the format painter?? | New Users to Excel |