Code from module does not work when i attach it to command button

M

Mr_Ragweed2

Board Regular
Joined
Nov 11, 2022
Messages
145
Office Version
  1. 365
Platform
  1. Windows
Hello to all. I have been working on a macro in a module and it does what i want it to do. I want to run the macro from a Command Button on a worksheet. I copied and pasted it making sure not to double up the "Sub" commands - beginning or end. Now the macro does not run. I get errors immediately on simple copy paste code.
Why would this be happening? I will post the code below. (I apologige for the clunkiness but like i said it works just fine when i Run it from the module it is in.)

I'm not asking anyone to rework all of my code for me. i just don't know why it doesn't work when i make it the code for the button.

VBA Code: 
Private Sub CommandButton2_Click()
'CUSTOMER AND PRODUCT INFO TRANSFER
    'this code needs put onto EACH vendor sheet and attached to a button
'-------------------------------------------------------------------------------------------------------
' transfers customer account data to order summary sheet

Application.ScreenUpdating = False

    Sheets("Dekalb Seed Order Form").Select
    Range("A1").Select

'hard stop requiring salesman box to be filled out
If Sheets("Dekalb Seed Order Form").Range("J12") = "" Then  'I HAVE MADE SURE THIS IS NOT THE ISSUE
    MsgBox "You must enter a salesman to continue."
End If

If Sheets("Customer info").Range("B1") = "" Then
Sheets("Dekalb Seed Order Form").Select

    Range("B6:M6").Select
    Selection.Copy
    Sheets("Customer info").Select
    Range("B1").Select  'tHIS IS WHERE I GET MY FIRST ERROR (Select method of range class failed)
    ActiveSheet.Paste
    
    With Selection
        .UnMerge
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Application.CutCopyMode = False
    Sheets("Dekalb Seed Order Form").Select
    Range("B8:G8").Copy
    Sheets("Customer info").Select
    Range("B2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.UnMerge
    Sheets("Dekalb Seed Order Form").Select
    Range("H8:K8").Copy
    Sheets("Customer info").Select
    Range("B3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.UnMerge
    Sheets("Dekalb Seed Order Form").Select
    Range("L8:M8").Copy
    Sheets("Customer info").Select
    Range("B4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.UnMerge
    Sheets("Dekalb Seed Order Form").Select
    Range("G10:I10").Copy
    Sheets("Customer info").Select
    Range("B5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.UnMerge
    Sheets("Dekalb Seed Order Form").Select
    Range("B10:F10").Copy
    Sheets("Customer info").Select
    Range("B6").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.UnMerge
    Sheets("Dekalb Seed Order Form").Select
    Range("J12:L12").Copy
    Sheets("Customer info").Select
    Range("B7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.UnMerge
    
    Sheets("Customer info").Select
    Columns("B:N").Select
    Selection.Font.Bold = True
    Selection.Font.Bold = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Columns("B:N").Select
    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
    
'organizes customer info into table for userform
    Range("B1").Copy
    Range("B12").PasteSpecial
    Range("B2").Copy
    Range("B14").PasteSpecial
       With Selection
        .HorizontalAlignment = xlLeft
    End With
    Range("B5").Copy
    Range("B16").PasteSpecial
    Range("B3").Copy
    Range("E14").PasteSpecial
         With Selection
        .HorizontalAlignment = xlLeft
    End With
    Range("B6").Copy
    Range("G12").PasteSpecial
        With Selection
        .HorizontalAlignment = xlLeft
    End With
    Range("B4").Copy
    Range("G14").PasteSpecial
        With Selection
        .HorizontalAlignment = xlLeft
    End With
    Range("B7").Copy
    Range("G16").PasteSpecial
    
    Range("D14,F12,F14,F16").Select
        With Selection
            .Font.Bold = True
            .Font.Underline = xlUnderlineStyleSingle
        End With
    End If
'------------------------------------------------------------------------------------------------------------

'transfers actual order data from vendor sheet to summary sheet

If Sheets("Dekalb Seed Order Form").Range("C19") = "" Then
    MsgBox "No products have been selected. To return to the Master Seed Order Form, please Select the 'Return to Master Seed Form' Button"
End If

'ElseIf Cells(i, 3).Value = "" Then ' this is wrong. needs to be treated as an error handler instead probably

If Sheets("Customer info").Range("B1") <> "" Then

Sheets("Dekalb Seed Order Form").Select
 
    Dim ThisFinal As Long
    Dim i As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet

    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 'new line
    
    For i = 19 To 31
        
        If DekalbWS.Cells(i, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(i).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
    
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
            
        End If
    Next i
    OSumWS.UsedRange.Columns.AutoFit
    Sheets("Dekalb Seed Order Form").Activate
    '----------------------------------------------------------------------------------------
    'below this line needs relocate to next available row after all product rows have been copied - works
               
     Dim copyRange1 As Range
     Dim copyRange2 As Range
     Dim copyRange3 As Range
     Dim copyRange4 As Range
     
     Dim cel As Range
     Dim pasteRange1 As Range
     Dim pasteRange2 As Range
     Dim pasteRange3 As Range
     Dim pasteRange4 As Range
     
     Dim FinalColumn As Long
     
     Set copyRange1 = Sheets("Dekalb Seed Order Form").Range("T39")
     Set copyRange2 = Sheets("Dekalb Seed Order Form").Range("T47")
     Set copyRange3 = Sheets("Dekalb Seed Order Form").Range("T57")
     Set copyRange4 = Sheets("Dekalb Seed Order Form").Range("N61")
     
     Set pasteRange1 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange2 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange3 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange4 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
 
     For Each cel In copyRange1
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -6).Column
        pasteRange1.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Next
     
     For Each cel In copyRange2
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -5).Column
        pasteRange2.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
      For Each cel In copyRange3
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -4).Column
        pasteRange3.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
       For Each cel In copyRange4
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -3).Column
        pasteRange4.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
        Application.CutCopyMode = False
    
    End If
    
  Sheets("Order Summary").Select
        Range("K:T").Select
        Selection.NumberFormat = "$#,##0.00"
    
'here remerges the cells
'This should really be a loop, but it works this way

Sheets("Dekalb Seed Order Form").Select

Range("D19:E19").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F19:G19").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H19:I19").Select
    Selection.Merge
Range("J19:K19").Select
    Selection.Merge
Range("L19:M19").Select
    Selection.Merge
    
Range("D20:E20").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F20:G20").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H20:I20").Select
    Selection.Merge
Range("J20:K20").Select
    Selection.Merge
Range("L20:M20").Select
    Selection.Merge
    
Range("D21:E21").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F21:G21").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H21:I21").Select
    Selection.Merge
Range("J21:K21").Select
    Selection.Merge
Range("L21:M21").Select
    Selection.Merge
    
Range("D22:E22").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F22:G22").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H22:I22").Select
    Selection.Merge
Range("J22:K22").Select
    Selection.Merge
Range("L22:M22").Select
    Selection.Merge
    
Range("D23:E23").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F23:G23").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H23:I23").Select
    Selection.Merge
Range("J23:K23").Select
    Selection.Merge
Range("L23:M23").Select
    Selection.Merge
    
Range("D24:E24").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F24:G24").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H24:I24").Select
    Selection.Merge
Range("J24:K24").Select
    Selection.Merge
Range("L24:M24").Select
    Selection.Merge
    
Range("D25:E25").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F25:G25").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H25:I25").Select
    Selection.Merge
Range("J25:K25").Select
    Selection.Merge
Range("L25:M25").Select
    Selection.Merge
    
Range("D26:E26").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F26:G26").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H26:I26").Select
    Selection.Merge
Range("J26:K26").Select
    Selection.Merge
Range("L26:M26").Select
    Selection.Merge
    
Range("D27:E27").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F27:G27").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H27:I27").Select
    Selection.Merge
Range("J27:K27").Select
    Selection.Merge
Range("L27:M27").Select
    Selection.Merge
    
Range("D28:E28").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F28:G28").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H28:I28").Select
    Selection.Merge
Range("J28:K28").Select
    Selection.Merge
Range("L28:M28").Select
    Selection.Merge
    
Range("D29:E29").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F29:G29").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H29:I29").Select
    Selection.Merge
Range("J29:K29").Select
    Selection.Merge
Range("L29:M29").Select
    Selection.Merge
    
Range("D30:E30").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F30:G30").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H30:I30").Select
    Selection.Merge
Range("J30:K30").Select
    Selection.Merge
Range("L30:M30").Select
    Selection.Merge
    
Range("D31:E31").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("F31:G31").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
    End With
Range("H31:I31").Select
    Selection.Merge
Range("J31:K31").Select
    Selection.Merge
Range("L31:M31").Select
    Selection.Merge
 



End Sub

Thank you very much for your time.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Use the debugger to set a breakpoint at the first line of code, say this one:
VBA Code: 
Application.ScreenUpdating = False

Then run using the button. When the code stops at the breakpoint, use the F8 key to single-step execution to gain more information about where & how the code is failing.
 
Upvote 0
steve the fish said:
Or probably easier put the code in standard module then call it from button click.
Click to expand...
oooh. I like "probably easier". haha

However, by putting ActiveSheet in front of all my ranges worked. Thank you very much!
Question: Why did that make a difference? isn't it the same thing when i already activate each sheet before the relevant bit of code? Or is because i "Activate" it that i need to use ActiveSheet vs "Selecting" a sheet?
 
Upvote 0
With the code in a standard module any unqualified ranges will refer to the active, however when the code is in a Sheet module those ranges will refer to the sheet the code is in.
 
Upvote 0
Thank you very much. I like that explanation. It is more eloquent than version i had i my head.
I love this forum!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
Lacan
  • Solved
Make VBA codes according to one specific sheet and not all entire workbook?
2
Replies
11
Views
396
Lacan
Lacan
I
  • Solved
Simplify my working code please
Replies
7
Views
186
ipbr21054
I
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
I
  • Question Question
Value not shown in ComboBox1 when code run
Replies
1
Views
58
ipbr21054
I

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top