Paste Values Works Different w/Form Control vs ActiveX Control - Why? Workaround?

bvbull200

New Member
Joined
Jul 29, 2013
Messages
24
I have the code below as both a Private Sub in an ActiveX button and as a standard Macro that is run by a Form Control button. It is copied and pasted from the Macro to the ActiveX Sub, so I know it is character for character the same thing. The only thing that is different is the name of the Sub.

VBA Code:
'This is for material update
Dim WorkbookTextFile As Workbook
Dim Template As Workbook
Set Template = ThisWorkbook
Dim ConfirmTemplateSave As Integer
Dim LastRow As Long
'LastRow = ActiveSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
LastRow = ActiveSheet.Range("A" & Rows.count).End(xlUp).Row

ConfirmTemplateSave = MsgBox("Have you saved your template in case it needs to be edited?", vbYesNo, "Confirm Template Save")

If ConfirmTemplateSave = vbNo Then
Exit Sub
Else
   
    Set WorkbookTextFile = Workbooks.Add
    Template.Sheets("Material Update Template").Activate
    Range(Cells(1, 1), Cells(LastRow, 123)).Copy
    WorkbookTextFile.Sheets("Sheet1").Activate
    Range("A1").PasteSpecial xlPasteValues
    Range(Cells(1, 29), Cells(LastRow, 29)).NumberFormat = "m/d/yyyy"
    Range(Cells(1, 65), Cells(LastRow, 65)).NumberFormat = "m/d/yyyy"
    Application.DisplayAlerts = False
    fname = Application.GetSaveAsFilename(fileFilter:="Yellow Box Formatted File (*.txt), *.txt", Title:="Save As")
    ActiveWorkbook.SaveAs fname, FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End If

When I run them, the Macro version runs fine and it pastes values from the initial sheet to the new sheet that gets created. When I run the Private Sub from the ActiveX Control, it doesn't ever paste anything. Running the Private Sub step by step, this is where nothing happens:

VBA Code:
    Range(Cells(1, 1), Cells(LastRow, 123)).Copy
    WorkbookTextFile.Sheets("Sheet1").Activate
    Range("A1").PasteSpecial xlPasteValues

It doesn't paste anything. If I stop there, then go press enter on the worksheet, it pastes the values and I can continue to run the Sub. I have tried adding a Applications.SendKey command, for the {Enter} button, but that doesn't work either. I end up with a blank worksheet with nothing pasted to it.

Does anyone have any clues as to why this is behaving differently depending on where I run it from and how to get the xlPasteValues to actually paste the values?

I appreciate any input!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
It's nothing to do with the difference between a form control & an ActiveX control, it's to do with the location of the code.
If the code is in a regular module, any unqualified ranges will work on the active sheet, however when the code is in a sheet module, the unqualified ranges look at the sheet belonging to the code module.
So if the code is in the code module of a sheet called "Sheet2" these lines
Rich (BB code):
   Range(Cells(1, 1), Cells(LastRow, 123)).Copy
    WorkbookTextFile.Sheets("Sheet1").Activate
    Range("A1").PasteSpecial xlPasteValues
will both be looking at sheet2 not sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,129,404
Messages
5,636,091
Members
416,898
Latest member
imsorrymen

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
Top