ExcelBanter - Macro Error in Command Button

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro Error in Command Button (https://www.excelbanter.com/excel-programming/421819-macro-error-command-button.html)

Macro Error in Command Button

I have recorded a macro that runs fine when I use the Macro Run command, but
when I copy the macro text and assign it to a command button it gives an
error. The following is the macro:

It gets hung up on the 2nd line (Rows...)

Any idea why it would work as a macro and not when placed into a command
button?

Sheets("Jacobs-Planning").Select
Rows("1:343").Select
Selection.Clear
Range("B7").Select
ActiveWindow.SmallScroll Down:=-30
Range("A5").Select
Range("MasterData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("Master Sheet").Range("A2:A3"), CopyToRange:=Range("A6"),
Unique:= _
False
Cells.Select
With Selection.Font
.Name = "Calibri"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H6").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

Macro Error in Command Button

What kind of command button (Forms toolbar or Controls Toolbox)? Where did
you paste the code? What version of Excel is this?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

"Paul" wrote in message
...
I have recorded a macro that runs fine when I use the Macro Run command,
but
when I copy the macro text and assign it to a command button it gives an
error. The following is the macro:

It gets hung up on the 2nd line (Rows...)

Any idea why it would work as a macro and not when placed into a command
button?

Sheets("Jacobs-Planning").Select
Rows("1:343").Select
Selection.Clear
Range("B7").Select
ActiveWindow.SmallScroll Down:=-30
Range("A5").Select
Range("MasterData").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:= _
Sheets("Master Sheet").Range("A2:A3"), CopyToRange:=Range("A6"),
Unique:= _
False
Cells.Select
With Selection.Font
.Name = "Calibri"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H6").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

Macro Error in Command Button

It was a control toolbox, but I changed it to a Forms Button at it appears
to work.

It is excel 2003.

I think this is solved.

Thanks

Macro Error in Command Button

If this macro is assigned to a commandbutton placed on a worksheet (from the
control toolbox), then part of the problem is then unqualified ranges.

Those unqualified ranges (like rows("1:343") will refer to the sheet that owns
the code--not the activesheet. This is different than code placed in a general
module.

And since you can only select a range on a worksheet that is active, the kind of
thing blows up:

Sheets("Jacobs-Planning").Select
Rows("1:343").Select

This may work for you:

Option Explicit
Private Sub CommandButton1_Click()
With Worksheets("Jacobs-Planning")
.Rows("1:343").Clear 'use .clearcontents to keep the formatting

.Range("MasterData").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Master Sheet").Range("A2:A3"), _
CopyToRange:=.Range("A6"), _
Unique:=False

With .Cells.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With

End Sub

This line may have to change:

.Range("MasterData").AdvancedFilter _

to something like:

worksheets("Someothersheetnamehere").Range("Master Data").AdvancedFilter _

I wasn't sure what worksheet owned the MasterData range, so I guessed
"jacobs-planning".

Paul wrote:

I have recorded a macro that runs fine when I use the Macro Run command, but
when I copy the macro text and assign it to a command button it gives an
error. The following is the macro:

It gets hung up on the 2nd line (Rows...)

Any idea why it would work as a macro and not when placed into a command
button?

Sheets("Jacobs-Planning").Select
Rows("1:343").Select
Selection.Clear
Range("B7").Select
ActiveWindow.SmallScroll Down:=-30
Range("A5").Select
Range("MasterData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("Master Sheet").Range("A2:A3"), CopyToRange:=Range("A6"),
Unique:= _
False
Cells.Select
With Selection.Font
.Name = "Calibri"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H6").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

--

Dave Peterson

Macro Error in Command Button

I should have pointed out that I've written about these two ways to assign
macros to buttons:

http://peltiertech.com/WordPress/200...tton-or-shape/
http://peltiertech.com/WordPress/200...tivex-control/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

"Paul" wrote in message
...

It was a control toolbox, but I changed it to a Forms Button at it appears
to work.

It is excel 2003.

I think this is solved.

Thanks