VBA Selection Change Event

[decadence]

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

 

[gallen]

Not sure I quite understand,

But this works with an ActiveX button named cmdTest:

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]

This is what I have so far....

Thisworkbook 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:

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:

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:

<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>