Code fires when file opens - looking to fire code only when

[mphansen]

Hi guys -
2 VB items I'm hoping someone could at look at and tell me what I'm doing wrong:

Item #1 - I'm trying to have a macro fire only when a command button is pressed. The current code works when the button is pressed, but it also fires when I open the book and I don't need it to fire upon opening.

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Update Monthly Returns" Then
Run "PullInReturns()"
End If
End Sub

Item #2 - I'm trying to get part of the code below to also paste formats ? Any suggestions on the code to include ?


On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only
.Value = .Value
End With

Thanks,
Mark

 

[Joe Was]

Your problem is in your If test, the caption should not be used as a test. Change or remove the Caption test and it should work.

Sub sPaste()
'This code pastes the format and value of the current selection,
'to the active range. The option has been set to Hot-key Ctrl-s

ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Sub CopyCutSelect()
'Use Options to set a Hot-Key for this.
ActiveCell.Select
Selection.Cut
'Selection.Copy
End Sub

Sub PasteSelect()
'Use Options to set a Hot-Key for this.
ActiveCell.Select
ActiveSheet.Paste
End Sub

 

[mphansen]

Joe - the first suggestion resolved the firing issue - thanks.

Regarding the second solution - I probably should have mentioned that the rest of the code is copying a closed workbook sheet, with the entire code as such:

Sub PullInReturns()
'''''''''''''''''''''''''''''''
'Written By me04518 (Mark Hansen)

'Pulls in all data from sheet1 of a closed workbook.
''''''''''''''''''''''''''''''''

Dim AreaAddress As String

'Clear sheet ready for new data
Sheet1.UsedRange.Clear
'Reference the UsedRange Address of Sheet1 _
in the closed Workbook.
Sheet1.Cells(1, 1) = "= 'E:Shared FilesPortfolio MgmtToolbox" _
& "[Manager Analysis_new.xls]ReturnData'!RC"
'Pass the area Address to a String
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Range(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not _
empty the pull in it's content, else put in an Error.
.FormulaR1C1 = "=IF('E:Shared FilesPortfolio MgmtToolbox" _
& "[Manager Analysis_new.xls]ReturnData'!RC="""","""",'E:Shared FilesPortfolio Mgmt" _
& "Toolbox[Manager Analysis_new.xls]ReturnData'!RC)"
'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only
.Value = .Value
End With
End Sub

How does your suggestion fit into the above statements ?

Thanks again - Mark