I have a macro that modifies one of 5 objects on an Excel worksheet.
After ending the macro, the object retains its 'handles' as being the selected one.
How can I 'de-select' it while still in the VB macro?
I have a macro that modifies one of 5 objects on an Excel worksheet.
After ending the macro, the object retains its 'handles' as being the selected one.
How can I 'de-select' it while still in the VB macro?
Select a cell on the worksheet. One idea is to save the location of the active cell before you start the macro and then re-select the saved location before you exit the macro.
Dim rAC As Range Set rAC = ActiveCell ' ' your code ' ' done rAC.Select
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Do you need to select the object to work with it?
If posting code please use code tags, see here.
Hi Norie,
Is there a way to do the following without using 'select'?
ActiveSheet.Shapes.Range(Array(btnName)).Select With Selection.Font .FontStyle = newStyle .Size = newSize End With
I tried combining the first two lines but got an error.
With ActiveSheet.Shapes.Range(Array(btnName)).Font .FontStyle = newStyle .Size = newSize End With
Thanks
I was considering that but was wondering if there was some other way.
- Stu
@Norie: good thought
I think that there is "always" an active cell. You cant not have one, but like stated above you dont have to interact with it. You want to limit interaction between VBA & Excel, they are not 2 programs under 1 umbrella but 2 completely different programs that why there is a penalty to select/activate, ect..
Is btnName a button?
If it is how did you create it?
Can you post the rest of the code so we can see what newStyle, newSize etc are?
Hi Norie
I'm using Excel 11 on a Mac; the workbook will be used on both Mac & Windows systems.
Heres the full subprogram; the purpose is to set a specified button to have the new characteristics.
Sub highlightBtn( btnName As String, code As Integer ) ' Adjusts appearance of a given button ' When code <> 0, highlight it with larger & bold text ' When code = 0, use smaller, plain text Dim newStyle As String Dim newSize As Integer If code = 0 Then newStyle = "Regular" newSize = 10 Else newStyle = "Bold" newSize = 12 End If ActiveSheet.Shapes.Range(Array(btnName)).Select With Selection.Font .FontStyle = newStyle .Size = newSize End With End Sub
Since its a single button, you don't need to use a shape range or selection
Sub highlightBtn(btnName As String, code As Integer) ' Adjusts appearance of a given button ' When code <> 0, highlight it with larger & bold text ' When code = 0, use smaller, plain text Dim newStyle As String Dim newSize As Integer If code = 0 Then newStyle = "Regular" newSize = 10 Else newStyle = "Bold" newSize = 12 End If With ActiveSheet.Shapes(btnName).TextFrame.Characters.Font .FontStyle = newStyle .Size = newSize End With End Sub
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
many thanks mikerikson,
That worked perfectly.
I had tried .TextFrame but missed .Characters.
Best wishes,
- Stu
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks