Strange behavior with command button.

[Ryan_s]

I created a command button to run some vba code but for some reason when the excel file is first opened the button isn't clickable. Only after closing the file then reopening it becomes clickable. Seems like every other time the file is opened is only when the command button is clickable.

When it's not available the cursor shows a a thick + sign when it's over the command button.
When it's available the cursor shows an arrow when it's over the command button.

1st open - not available
2nd open - available
3rd open - not available
4th open - available
etc.

One other thing to mention is then when I close the file each time without making any changes it asks if I want to save changes. Maybe something is changing on the file each time it's opened?

Edit: So I tested the sequence again and if I choose "don't save changes" when closing the availability of he command button remains the same next time it's opened. So something is being changed on the file in the background each time it's opened. Still can't figure out what's happening.

Hopefully I can get to the bottom of what's going on here.
Thanks!

 

[6StringJazzer]

This is not normal behavior. The problem may be buried somewhere in your VBA code, which we can't see.

Can you share your VBA code? All of the code, every module.

 

[Ryan_s]

Here's all the VBA code on the workbook. Just really just the one command button click plus a couple of sort macros that use their own form control button.

Private Sub CommandButton1_Click()

Dim lastrow As Long, erow As Long

lastrow = Worksheets("Options Sell").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow


    If Worksheets("Options Sell").Cells(i, 17).Value = "Log Assignment" Then
   
        Worksheets("Options Sell").Cells(i, 1).Copy
       
        erow = Worksheets("Stocks").Cells(Rows.Count, 1).End(xlUp).Row
       
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 1)
       
        Worksheets("Options Sell").Cells(i, 17).Value = "Assigned"
       
        Worksheets("Options Sell").Cells(i, 5).Copy
       
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 3)
       
        Worksheets("Options Sell").Cells(i, 6).Copy
       
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 2)
       
        'Worksheets("Options Sell").Cells(i, 17).Copy
       
        'Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 12)
       
        Worksheets("Stocks").Cells(erow + 1, 12).Value = "From Assignment"
   
   
    End If

Next i

Application.CutCopyMode = False

End Sub

Sub Sort()
'
' Sort Macro
'

'
    ActiveWorkbook.Worksheets("Options Sell").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Options Sell").Sort.SortFields.Add Key:=Range( _
        "N2:N499"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Options Sell").Sort.SortFields.Add Key:=Range( _
        "I2:I499"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Options Sell").Sort
        .SetRange Range("A1:T499")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub Sort_Buy()
'
' Sort_Buy Macro
'

'
    Range("J8").Select
    ActiveWorkbook.Worksheets("Options Buy").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Options Buy").Sort.SortFields.Add Key:=Range( _
        "N2:N501"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Options Buy").Sort.SortFields.Add Key:=Range( _
        "I2:I501"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Options Buy").Sort
        .SetRange Range("A1:T501")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub Sort_Stocks()
'
' Sort_Stocks Macro
'

'
    ActiveWorkbook.Worksheets("Stocks").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Stocks").Sort.SortFields.Add Key:=Range("F2:F226") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Stocks").Sort.SortFields.Add Key:=Range("E2:E226") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Stocks").Sort
        .SetRange Range("A1:O226")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Buttons.Add(1131.75, 50.625, 72, 72).Select
    Selection.OnAction = "Sort_Stocks"
    ActiveSheet.Shapes("Button 1").IncrementLeft -7.5
    ActiveSheet.Shapes("Button 1").IncrementTop -14.6249606299
    ActiveSheet.Shapes("Button 1").IncrementLeft 2.25
    ActiveSheet.Shapes("Button 1").IncrementTop 0.3750393701
    Selection.Characters.Text = "Sort"
    With Selection.Characters(Start:=1, Length:=4).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("M6").Select
    ActiveWorkbook.Save
End Sub

Thanks!

 

[6StringJazzer]

I don't see any suspicious code there. For the moment I'm stuck. I'm guessing it's an ActiveX button.

 

[Ryan_s]

I don't see any suspicious code there. For the moment I'm stuck. I'm guessing it's an ActiveX button.

For the command code yes it's an ActiveX button.

 

[Ryan_s]

You were right, looks like it's an issue with the ActiveX button. I moved the code over to a form control button and deleted the Active X and no longer have the issue. Thank for your help on this. I'll just run it from the form control for now.

 

[6StringJazzer]

I'm honestly surprised that solved it, because I've never had this happen with ActiveX controls. But I'm glad you found a workaround.