VBA Selection Change Event

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
524
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Hi, I have code which will enable a button if a column is selected but the selection change event doesn't recognise when an offset column is selected via button code, is there a way to detect this offset change without clicking the column to select it? Can someone help with this please

What I would like to do:

Column B is selected
Button Enables
Enabled Button Clicked which changes the selected column to column A without clicking on column A
Enabled button disables
If column A is selected then button disables

What happens now:

Column B is selected
Button Enables
Enabled Button Clicked which changes the selected column to column A without clicking on column A
Enabled button stays enabled
If column A is selected then button disables
 
Last edited:

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Not sure I quite understand,

But this works with an ActiveX button named cmdTest:

Code:
Private Sub cmdTest_Click()
    Range("A1").Select
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    cmdTest.Enabled = Target.Column = 2
End Sub
 

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
524
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
This is what I have so far....

Thisworkbook Code:
Code:
Option Explicit
Public WithEvents App As Application

Private Sub Workbook_Open()
     Set App = Application
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Set App = Nothing
End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     If Not Target Is Nothing Then
     If Target.Cells.CountLarge / Target.Columns.Count = 1048576 Then
          If Target.Columns.Count > 1 Then
               Call DisableAll ' Refreshes Ribbon and Disables all buttons
          Else
               If Target.Count = Rows.Count And Target.Column = 1 Then ' If Column Selected is the First Column
                    Call EnableColumnRight ' Refreshes Ribbon and Enables Button "MoveColumnRight"
               ElseIf Target.Count = Rows.Count And Target.Column = 16384 Then ' If Column Selected is the Last Column
                    Call EnableColumnLeft ' Refreshes Ribbon and Enables Button "MoveColumnLeft"
               Else
                    Call EnableColumns 'Refreshes Ribbon and Enables Both Buttons
               End If
          End If
     End If
     End If
End Sub

Move Module:
Code:
Public Sub MoveColumnLeft(control As IRibbonControl)
'
    'On Error Resume Next
    'If Selection.Cells.CountLarge / Selection.Rows.Count = 16384 Then Exit Sub
    'If Selection.Cells.CountLarge / Selection.Columns.Count < 1048576 Then Exit Sub
    'If Selection.Columns.Count > 1 Then Exit Sub
    With Selection.EntireColumn
        .Cut
        .Offset(0, -1).Insert
        .Select
    End With
End Sub
Public Sub MoveColumnRight(control As IRibbonControl)
'
    'On Error Resume Next
    'If Selection.Cells.CountLarge / Selection.Rows.Count = 16384 Then Exit Sub
    'If Selection.Cells.CountLarge / Selection.Columns.Count < 1048576 Then Exit Sub
    'If Selection.Columns.Count > 1 Then Exit Sub
    With Selection.EntireColumn
        .Cut
        .Offset(0, 2).Insert
        .Select
    End With
End Sub

Button Visibilty Module:
Code:
Option Explicit
Public iRib As IRibbonUI
Public MyTag As String
Sub ToolsLoad(ribbon As IRibbonUI)
    Set iRib = ribbon
 End Sub
 
Sub GetEnabledMacro(control As IRibbonControl, ByRef Enabled)
    If MyTag = "Enable" Then
        Enabled = True
    Else
        If control.Tag Like MyTag Then
            Enabled = True
        Else
            Enabled = False
        End If
    End If
End Sub
Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If iRib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        iRib.Invalidate
    End If
End Sub
Sub EnableColumns()
'Enable Button Group 1 controls (Move Left and Move Right buttons)
    Call RefreshRibbon(Tag:="Group1*")
End Sub
Sub EnableColumnLeft()
'Enable Button Group 1 Button 1 control (Move Left button)
    Call RefreshRibbon(Tag:="Group1Button1")
End Sub
Sub EnableColumnRight()
'Enable Button Group 1 Button 2 control (Move Right button)
    Call RefreshRibbon(Tag:="Group1Button2")
End Sub
Sub DisableAll()
'Disable all controls
    Call RefreshRibbon(Tag:="")
End Sub

XML Code:
HTML:
<customUI xmlns="[URL]http://schemas.microsoft.com/office/2009/07/customui[/URL]" ******="ToolsLoad">
<ribbon>
<tabs>
<tab id="MyTools" label="Tools" >
<group id="MoveColumn" label="Move Columns" tag="MyGroup1" >
 <button id="G1B1" tag="Group1Button1" imageMso="GoRtl" onAction="MoveColumnLeft" getEnabled="GetEnabledMacro" supertip="Move the Selected Column Left"  label="Left" size="large" />
 <button id="G1B2" tag="Group1Button2" imageMso="GoLeftToRight" onAction="MoveColumnRight" getEnabled="GetEnabledMacro" supertip="Move the Selected Column Right" label="Right" size="large" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top