Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > macro does not work from commandbutton
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default macro does not work from commandbutton
I have the following macro that works just fine from the toolbar but when I
attach the code to a newly created commandbutton, it runs into a "run-time
error '1004' select method of range-class failed" and the debugger highlights
the "Cells.Select" after opening the g: file. I have set the
takefocusonclick to false but that still does me no good. Anyone out there
that can help me. I am fairly new a VB and can't solve this one on my own.
Thanks.

ChDir "g:\"
Workbooks.Open Filename:="g:\morning packet.xls"
Cells.Select
Selection.Copy
Windows("SFL Journal 2.xls").Activate
Sheets("Morning Packet Import").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-117
Range("R34:T34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-14.27"
Range("R68:T68").Select
ActiveCell.FormulaR1C1 = "-2.68"
Range("E102:F102").Select
ActiveCell.FormulaR1C1 = "$58.57 "
Range("L102:O102").Select
ActiveCell.FormulaR1C1 = "65.57"
Range("V102:X102").Select
ActiveCell.FormulaR1C1 = "$89.31 "
Range("AD102:AF102").Select
ActiveCell.FormulaR1C1 = "$46.83 "
Range("AM102:AO102").Select
ActiveCell.FormulaR1C1 = "1.91"
Range("E136:F136").Select
ActiveCell.FormulaR1C1 = "$57.00 "
Range("L136:O136").Select
ActiveCell.FormulaR1C1 = "57.78"
Range("V136:X136").Select
ActiveCell.FormulaR1C1 = "$98.51 "
Range("AD136:AF136").Select
ActiveCell.FormulaR1C1 = "$49.18 "
Range("AM136:AO136").Select
ActiveCell.FormulaR1C1 = "2"
Sheets("Journal").Select
ActiveWindow.SmallScroll Down:=-6
Windows("morning packet.xls").Activate
ActiveWindow.Close
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default macro does not work from commandbutton
ChDir "g:\"
Workbooks.Open Filename:="g:\morning packet.xls"
With ActiveSheet
.Cells.Select
Selection.Copy
End with
Windows("SFL Journal 2.xls").Activate
With Sheets("Morning Packet Import")
.Select
.Cells.Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-117
.Range("R34:T34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-14.27"
.Range("R68:T68").Select
ActiveCell.FormulaR1C1 = "-2.68"
.Range("E102:F102").Select
ActiveCell.FormulaR1C1 = "$58.57 "
.Range("L102:O102").Select
ActiveCell.FormulaR1C1 = "65.57"
.Range("V102:X102").Select
ActiveCell.FormulaR1C1 = "$89.31 "
.Range("AD102:AF102").Select
ActiveCell.FormulaR1C1 = "$46.83 "
.Range("AM102:AO102").Select
ActiveCell.FormulaR1C1 = "1.91"
.Range("E136:F136").Select
ActiveCell.FormulaR1C1 = "$57.00 "
.Range("L136:O136").Select
ActiveCell.FormulaR1C1 = "57.78"
.Range("V136:X136").Select
ActiveCell.FormulaR1C1 = "$98.51 "
.Range("AD136:AF136").Select
ActiveCell.FormulaR1C1 = "$49.18 "
.Range("AM136:AO136").Select
ActiveCell.FormulaR1C1 = "2"
End With
Sheets("Journal").Select
ActiveWindow.SmallScroll Down:=-6
Windows("morning packet.xls").Activate
ActiveWindow.Close
End Sub

--
Regards,
Tom Ogilvy


"ellflocko via OfficeKB.com" wrote:

I have the following macro that works just fine from the toolbar but when I
attach the code to a newly created commandbutton, it runs into a "run-time
error '1004' select method of range-class failed" and the debugger highlights
the "Cells.Select" after opening the g: file. I have set the
takefocusonclick to false but that still does me no good. Anyone out there
that can help me. I am fairly new a VB and can't solve this one on my own.
Thanks.

ChDir "g:\"
Workbooks.Open Filename:="g:\morning packet.xls"
Cells.Select
Selection.Copy
Windows("SFL Journal 2.xls").Activate
Sheets("Morning Packet Import").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-117
Range("R34:T34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-14.27"
Range("R68:T68").Select
ActiveCell.FormulaR1C1 = "-2.68"
Range("E102:F102").Select
ActiveCell.FormulaR1C1 = "$58.57 "
Range("L102:O102").Select
ActiveCell.FormulaR1C1 = "65.57"
Range("V102:X102").Select
ActiveCell.FormulaR1C1 = "$89.31 "
Range("AD102:AF102").Select
ActiveCell.FormulaR1C1 = "$46.83 "
Range("AM102:AO102").Select
ActiveCell.FormulaR1C1 = "1.91"
Range("E136:F136").Select
ActiveCell.FormulaR1C1 = "$57.00 "
Range("L136:O136").Select
ActiveCell.FormulaR1C1 = "57.78"
Range("V136:X136").Select
ActiveCell.FormulaR1C1 = "$98.51 "
Range("AD136:AF136").Select
ActiveCell.FormulaR1C1 = "$49.18 "
Range("AM136:AO136").Select
ActiveCell.FormulaR1C1 = "2"
Sheets("Journal").Select
ActiveWindow.SmallScroll Down:=-6
Windows("morning packet.xls").Activate
ActiveWindow.Close
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default macro does not work from commandbutton
That worked great!!! Thanks so much.

ellflocko wrote:
I have the following macro that works just fine from the toolbar but when I
attach the code to a newly created commandbutton, it runs into a "run-time
error '1004' select method of range-class failed" and the debugger highlights
the "Cells.Select" after opening the g: file. I have set the
takefocusonclick to false but that still does me no good. Anyone out there
that can help me. I am fairly new a VB and can't solve this one on my own.
Thanks.

ChDir "g:\"
Workbooks.Open Filename:="g:\morning packet.xls"
Cells.Select
Selection.Copy
Windows("SFL Journal 2.xls").Activate
Sheets("Morning Packet Import").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-117
Range("R34:T34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-14.27"
Range("R68:T68").Select
ActiveCell.FormulaR1C1 = "-2.68"
Range("E102:F102").Select
ActiveCell.FormulaR1C1 = "$58.57 "
Range("L102:O102").Select
ActiveCell.FormulaR1C1 = "65.57"
Range("V102:X102").Select
ActiveCell.FormulaR1C1 = "$89.31 "
Range("AD102:AF102").Select
ActiveCell.FormulaR1C1 = "$46.83 "
Range("AM102:AO102").Select
ActiveCell.FormulaR1C1 = "1.91"
Range("E136:F136").Select
ActiveCell.FormulaR1C1 = "$57.00 "
Range("L136:O136").Select
ActiveCell.FormulaR1C1 = "57.78"
Range("V136:X136").Select
ActiveCell.FormulaR1C1 = "$98.51 "
Range("AD136:AF136").Select
ActiveCell.FormulaR1C1 = "$49.18 "
Range("AM136:AO136").Select
ActiveCell.FormulaR1C1 = "2"
Sheets("Journal").Select
ActiveWindow.SmallScroll Down:=-6
Windows("morning packet.xls").Activate
ActiveWindow.Close
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i make a commandbutton run a macro?? Shax New Users to Excel 7 June 7th 06 05:16 PM
CommandButton Link to Macro KMassey1976 Excel Programming 2 March 22nd 06 08:27 PM
Can You Change A CommandButton Code Using A Macro? Donna[_7_] Excel Programming 4 February 11th 05 08:38 AM
Commandbutton in UserForm to run Macro salihyil[_4_] Excel Programming 2 February 16th 04 05:36 PM
Macro won't run from CommandButton Nathan Gutman Excel Programming 3 December 24th 03 07:59 PM


All times are GMT +1. The time now is 02:39 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement