Running macro from shortcut key vs button

[mmoore410]

Excel 2013
Win 7 pro


I have a macro, that based on information input on "sheet 1" copies calculations from 1 of 3 areas on "sheet 2" or "sheet 3" which are hidden and pastes them on "sheet 1"
When running the macro from the shortcut key there are no problems.
If the macro is run from a button (form control) placed on "sheet 1" it does not work unless "sheet 2 or 3" is unhidden.

Any idea why it works for the shortcut key but not the button?

Thanks.

Sub Get_Results()
'
' Get_Results Macro
'
' Keyboard Shortcut: Ctrl+g
'
'
Dim county As String
Dim condition As Integer
'
Application.ScreenUpdating = False
Sheets("ESD Requirement1A (CITY)").Visible = True
Sheets("ESD Requirement1A").Visible = True
    Range("c8:f8").Select
    county = ActiveCell.Value
'
    Range("a41").Select
    condition = ActiveCell.Value
'
Range("a65:k95").Select
    Selection.ClearContents
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'
If county = "Baltimore City" Then
    Sheets("ESD Requirement1A (CITY)").Select
Else
    Sheets("ESD Requirement1A").Select
End If
'
    If condition = 1 Then
                Range("A1:K13").Copy Sheets("ESD Sizing1A").Range("a65")
    End If
'
    If condition = 2 Then
                Range("A16:K41").Copy Sheets("ESD Sizing1A").Range("a65")
    End If
'
    If condition = 3 Then
                Range("A46:K73").Copy Sheets("ESD Sizing1A").Range("a65")
    End If
'
'
Sheets("ESD Requirement1A").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("ESD Requirement1A (CITY)").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("ESD Sizing1A").Select
Application.ScreenUpdating = True
Range("a65").Select
'
End Sub

 

[starl]

You're not saying why it doesn't work.

But looking at your code... it's all based on the active sheet when the program starts. You aren't specifying sheets with your ranges and just leaving it to luck.

For example:
Range("c8:f8").Select

That pertains to whatever sheet is active. Be more specific
Worksheets ("sheet name").Range("C8:F8").Select

(actually, absolute best coding practice would be declaring objects and not selecting cells but just making your changes, but let's keep it simple)

 

[mmoore410]

You're not saying why it doesn't work.

But looking at your code... it's all based on the active sheet when the program starts. You aren't specifying sheets with your ranges and just leaving it to luck.

For example:
Range("c8:f8").Select

That pertains to whatever sheet is active. Be more specific
Worksheets ("sheet name").Range("C8:F8").Select

(actually, absolute best coding practice would be declaring objects and not selecting cells but just making your changes, but let's keep it simple)

Thank you for the quick reply.

It worked fine if I used the shortcut key. When I activated the button that had the macro assigned, it wouldn't clear the previous contents and paste the new info.

I changed the lines as you suggested and received a "runtime error code 1004 method of range class failed"

I changed the lines to read:
Sheets("ESD Sizing1A").Select
Sheets("ESD Sizing1A").Range("a41").Select

and it now seems to be working with no issues.

Thank you very much.

I still do not understand why it would run correctly using the shortcut key but not work if the button was selected since it ran the same code from the same sheet.