TOOLBAR button or Keyboard Shortcut FOR Center Across Selection??

[RajenRajput1]

Hi,

I use Excel 2007 at work and Excel 2003 at home.

Is there a keyboard shortcut to center across selection for either of these
versions, pref 2007?

And secondly, does anyone know if there is a toolbar button for center
across selection, or perhaps a way of placing a button on the quick access
toolbar?

Merging cells is too easy to do and that is why I prefer it, however
formatting and selecting cells is becoming an issue with merging, and center
across selection has it's merits, but it's time consuming.

Thank for the advice, I hope someone can help.

Regards,

Rajen

 

[Bob Phillips]

If you create a macro that does it, you can add that macro to the QAT, it is
an option in the QAT customization.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RajenRajput1]

....

Does that mean that any macro can be added to the Quick Acess Toolbar, and
it will always appear each time Excel loads?

That's neat. I am sure I can find (or record and tweak) the code and do this.

Thanks.

 

[Bob Phillips]

If you put in personal (.xlsb in my case), yes it will be. Just create the
macro, right-click the QAT, select 'Customize the Quick Access Toolbar...',
choose Macros from the first dropdown, then select the macro from the list,
and Add>> it.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RajenRajput1]

Thank you for the help.

I made the macro and saved it in personal.xlsb and it works fine, ONLY when
one Excel window is open.

When I open another Excel window, I get a message saying something like,

"File In Use - Personal.xlsb is locked for editing" "Read only, Notify or
Cancel"

Which is soon going to get annoying as I open many windows many times. How
could I get around this ?

Thank you,

Rajen

 

[Gord Dibben]

Assign this to a button.

Sub TOGGLECENTERACROSS()
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
Selection.HorizontalAlignment = xlCenterAcrossSelection
End If
End With
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Open all workbooks in one instance of Excel rather a new instance Excel for each
workbook you open.


Gord Dibben MS Excel MVP

 

[RajenRajput1]

Opening all the files in one instant is great, however I prefer not to do it
as I can subsequently move and resize individual windows on the screen.

If there is no way to get around this, then so be it.

Thanks for the advice, well spotted !

Rajen

 

[RajenRajput1]

How do I do it ??

Is it the same Macro thing as Bob Phillip's response?

Kind regards,

Rajen

 

[Bob Phillips]

Even a read only personal will still have the macro available.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

That could be the macro that I was suggesting, although I wouldn't be so
profligate as to repeat Selection <g>

Sub TOGGLECENTERACROSS()
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
.HorizontalAlignment = xlCenterAcrossSelection
End If
End With
End Sub

But I was thinking of a more basic macro, not a toggle

Sub CentreAcrossSelection()
Selection.HorizontalAlignment = xlCenterAcrossSelection
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gord Dibben]

In one instance of Excel you can open multiple workbooks then go to Window>New
Window and Window>Arrange to get several windows with a workbook in each window.


Gord

 

[Gord Dibben]

It is a macro that you would assign to a button as Bob described for Excel 2007

Toggles the CAS on/off


Gord

 

[Gord Dibben]

Bob

Please elucidate on the profligacy of repeating "Selection"

I cannot get the macro to work without using xlCenterAcrossSelection even
though inside the "with selection"


Gord