Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
I have a .xlt with 2 macros in it.
The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
Do you have a sheet Named Assets?
"Simon" wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
Not in the .xlt but I don't run the macro in the xlt.
It is in the .xls though as it is created by Access. My other macro also refers to these new sheets in the same way without a problem albeit it only copies cells across. "Mike" wrote: Do you have a sheet Named Assets? "Simon" wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
First, if this code is associated with a commandbutton from the control toolbox
toolbar, then the code does belong behind the worksheet. And if your code that adds that worksheet named Assets has not run, then you won't have that worksheet to select. Option Explicit Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet 'since the code is behind the worksheet (summary) that owns 'the code, you can use the Me keyword to refer to that sheet With Me NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 With .Range("A" & NextRow) .Value = "Assets" .Font.Bold = True .Font.Size = 12 End With End With 'check to see if a worksheet named Assets really exists Set wks = Nothing On Error Resume Next Set wks = Me.Parent.Worksheets("assets") On Error GoTo 0 If wks Is Nothing Then MsgBox "No worksheet named Assets in this workbook!" Else wks.Select End If End Sub Any chance that your code was supposed to make a worksheet named Assets first??? Simon wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
Hi Dave,
Worksheet Assets is definately ok. I have now copied the macro to a Module and if I run that Module from VBA it works fine. All I need to know is how the user runs this module easily without delving into VB. A button somewhere that calls the module? How do I do this? Simon "Dave Peterson" wrote: First, if this code is associated with a commandbutton from the control toolbox toolbar, then the code does belong behind the worksheet. And if your code that adds that worksheet named Assets has not run, then you won't have that worksheet to select. Option Explicit Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet 'since the code is behind the worksheet (summary) that owns 'the code, you can use the Me keyword to refer to that sheet With Me NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 With .Range("A" & NextRow) .Value = "Assets" .Font.Bold = True .Font.Size = 12 End With End With 'check to see if a worksheet named Assets really exists Set wks = Nothing On Error Resume Next Set wks = Me.Parent.Worksheets("assets") On Error GoTo 0 If wks Is Nothing Then MsgBox "No worksheet named Assets in this workbook!" Else wks.Select End If End Sub Any chance that your code was supposed to make a worksheet named Assets first??? Simon wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
If this is a commandbutton from the control toolbox toolbar, then don't put the
code in a general module. If you really, really want to put the code in a general module, you'll still have to have the commandbutton2_click event call that procedure--so why bother????? If you change to a button from the forms toolbar, you can keep the code in a general module and assign the forms button to that procedure in that general module. Be prepared to have to reassign that macro each time the workbook is opened. Else the macro will still point at the code in the .xlt file. I think you'd be better off using a commandbutton and the code behind the worksheet in this situation. Simon wrote: Hi Dave, Worksheet Assets is definately ok. I have now copied the macro to a Module and if I run that Module from VBA it works fine. All I need to know is how the user runs this module easily without delving into VB. A button somewhere that calls the module? How do I do this? Simon "Dave Peterson" wrote: First, if this code is associated with a commandbutton from the control toolbox toolbar, then the code does belong behind the worksheet. And if your code that adds that worksheet named Assets has not run, then you won't have that worksheet to select. Option Explicit Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet 'since the code is behind the worksheet (summary) that owns 'the code, you can use the Me keyword to refer to that sheet With Me NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 With .Range("A" & NextRow) .Value = "Assets" .Font.Bold = True .Font.Size = 12 End With End With 'check to see if a worksheet named Assets really exists Set wks = Nothing On Error Resume Next Set wks = Me.Parent.Worksheets("assets") On Error GoTo 0 If wks Is Nothing Then MsgBox "No worksheet named Assets in this workbook!" Else wks.Select End If End Sub Any chance that your code was supposed to make a worksheet named Assets first??? Simon wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
Thanks for your help Dave.
I have now solved it by putting both macros in the on Open event and its fine. "Dave Peterson" wrote: If this is a commandbutton from the control toolbox toolbar, then don't put the code in a general module. If you really, really want to put the code in a general module, you'll still have to have the commandbutton2_click event call that procedure--so why bother????? If you change to a button from the forms toolbar, you can keep the code in a general module and assign the forms button to that procedure in that general module. Be prepared to have to reassign that macro each time the workbook is opened. Else the macro will still point at the code in the .xlt file. I think you'd be better off using a commandbutton and the code behind the worksheet in this situation. Simon wrote: Hi Dave, Worksheet Assets is definately ok. I have now copied the macro to a Module and if I run that Module from VBA it works fine. All I need to know is how the user runs this module easily without delving into VB. A button somewhere that calls the module? How do I do this? Simon "Dave Peterson" wrote: First, if this code is associated with a commandbutton from the control toolbox toolbar, then the code does belong behind the worksheet. And if your code that adds that worksheet named Assets has not run, then you won't have that worksheet to select. Option Explicit Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet 'since the code is behind the worksheet (summary) that owns 'the code, you can use the Me keyword to refer to that sheet With Me NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 With .Range("A" & NextRow) .Value = "Assets" .Font.Bold = True .Font.Size = 12 End With End With 'check to see if a worksheet named Assets really exists Set wks = Nothing On Error Resume Next Set wks = Me.Parent.Worksheets("assets") On Error GoTo 0 If wks Is Nothing Then MsgBox "No worksheet named Assets in this workbook!" Else wks.Select End If End Sub Any chance that your code was supposed to make a worksheet named Assets first??? Simon wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
Both?????
Simon wrote: Thanks for your help Dave. I have now solved it by putting both macros in the on Open event and its fine. "Dave Peterson" wrote: If this is a commandbutton from the control toolbox toolbar, then don't put the code in a general module. If you really, really want to put the code in a general module, you'll still have to have the commandbutton2_click event call that procedure--so why bother????? If you change to a button from the forms toolbar, you can keep the code in a general module and assign the forms button to that procedure in that general module. Be prepared to have to reassign that macro each time the workbook is opened. Else the macro will still point at the code in the .xlt file. I think you'd be better off using a commandbutton and the code behind the worksheet in this situation. Simon wrote: Hi Dave, Worksheet Assets is definately ok. I have now copied the macro to a Module and if I run that Module from VBA it works fine. All I need to know is how the user runs this module easily without delving into VB. A button somewhere that calls the module? How do I do this? Simon "Dave Peterson" wrote: First, if this code is associated with a commandbutton from the control toolbox toolbar, then the code does belong behind the worksheet. And if your code that adds that worksheet named Assets has not run, then you won't have that worksheet to select. Option Explicit Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet 'since the code is behind the worksheet (summary) that owns 'the code, you can use the Me keyword to refer to that sheet With Me NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 With .Range("A" & NextRow) .Value = "Assets" .Font.Bold = True .Font.Size = 12 End With End With 'check to see if a worksheet named Assets really exists Set wks = Nothing On Error Resume Next Set wks = Me.Parent.Worksheets("assets") On Error GoTo 0 If wks Is Nothing Then MsgBox "No worksheet named Assets in this workbook!" Else wks.Select End If End Sub Any chance that your code was supposed to make a worksheet named Assets first??? Simon wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
Yes. Why not?
"Dave Peterson" wrote: Both????? Simon wrote: Thanks for your help Dave. I have now solved it by putting both macros in the on Open event and its fine. "Dave Peterson" wrote: If this is a commandbutton from the control toolbox toolbar, then don't put the code in a general module. If you really, really want to put the code in a general module, you'll still have to have the commandbutton2_click event call that procedure--so why bother????? If you change to a button from the forms toolbar, you can keep the code in a general module and assign the forms button to that procedure in that general module. Be prepared to have to reassign that macro each time the workbook is opened. Else the macro will still point at the code in the .xlt file. I think you'd be better off using a commandbutton and the code behind the worksheet in this situation. Simon wrote: Hi Dave, Worksheet Assets is definately ok. I have now copied the macro to a Module and if I run that Module from VBA it works fine. All I need to know is how the user runs this module easily without delving into VB. A button somewhere that calls the module? How do I do this? Simon "Dave Peterson" wrote: First, if this code is associated with a commandbutton from the control toolbox toolbar, then the code does belong behind the worksheet. And if your code that adds that worksheet named Assets has not run, then you won't have that worksheet to select. Option Explicit Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet 'since the code is behind the worksheet (summary) that owns 'the code, you can use the Me keyword to refer to that sheet With Me NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 With .Range("A" & NextRow) .Value = "Assets" .Font.Bold = True .Font.Size = 12 End With End With 'check to see if a worksheet named Assets really exists Set wks = Nothing On Error Resume Next Set wks = Me.Parent.Worksheets("assets") On Error GoTo 0 If wks Is Nothing Then MsgBox "No worksheet named Assets in this workbook!" Else wks.Select End If End Sub Any chance that your code was supposed to make a worksheet named Assets first??? Simon wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to standard module
I didn't even see one complete macro in your previous posts. So the word "both"
surprised me. And if you were really trying to execute the code you pasted by clicking on a button (you did use "CommandButton2_Click" as the procedure name), I wouldn't have guessed that anything in the workbook_open event would help. But if you got it working, then that's good. Simon wrote: Yes. Why not? "Dave Peterson" wrote: Both????? Simon wrote: Thanks for your help Dave. I have now solved it by putting both macros in the on Open event and its fine. "Dave Peterson" wrote: If this is a commandbutton from the control toolbox toolbar, then don't put the code in a general module. If you really, really want to put the code in a general module, you'll still have to have the commandbutton2_click event call that procedure--so why bother????? If you change to a button from the forms toolbar, you can keep the code in a general module and assign the forms button to that procedure in that general module. Be prepared to have to reassign that macro each time the workbook is opened. Else the macro will still point at the code in the .xlt file. I think you'd be better off using a commandbutton and the code behind the worksheet in this situation. Simon wrote: Hi Dave, Worksheet Assets is definately ok. I have now copied the macro to a Module and if I run that Module from VBA it works fine. All I need to know is how the user runs this module easily without delving into VB. A button somewhere that calls the module? How do I do this? Simon "Dave Peterson" wrote: First, if this code is associated with a commandbutton from the control toolbox toolbar, then the code does belong behind the worksheet. And if your code that adds that worksheet named Assets has not run, then you won't have that worksheet to select. Option Explicit Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet 'since the code is behind the worksheet (summary) that owns 'the code, you can use the Me keyword to refer to that sheet With Me NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 With .Range("A" & NextRow) .Value = "Assets" .Font.Bold = True .Font.Size = 12 End With End With 'check to see if a worksheet named Assets really exists Set wks = Nothing On Error Resume Next Set wks = Me.Parent.Worksheets("assets") On Error GoTo 0 If wks Is Nothing Then MsgBox "No worksheet named Assets in this workbook!" Else wks.Select End If End Sub Any chance that your code was supposed to make a worksheet named Assets first??? Simon wrote: I have a .xlt with 2 macros in it. The .xlt is copied in code from Access to a .xls and then data is transferred from Access to the .xls Both of the macros are activated on click of seperate command buttons. The command buttons are in Sheet1 (Code) the sheet name is "Summary" Macro 1 runs fine but I am getting an error on the 2nd as below. Private Sub CommandButton2_Click() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Summary") FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With For iRow = FirstRow To LastRow Next iRow Range("A" & iRow).Select ActiveCell.Value = "Assets" ActiveCell.Font.Bold = True ActiveCell.Font.Size = 12 Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of range class failed I have discovered thanks to usergroup that this needs to be in a standard module. Can someone tell me how I do this and then how do I call it. Thank you -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro in excel to call a access module to run. | Excel Discussion (Misc queries) | |||
Assigning a module to a button macro | Excel Discussion (Misc queries) | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
How do I copy a new module to 200 workbooks with a macro? | Excel Worksheet Functions |