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

mphansen

New Member
Joined
Jun 23, 2002
Messages
45
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Jun 23, 2002
Messages
45
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
 

Forum statistics

Threads
1,143,707
Messages
5,720,387
Members
422,282
Latest member
psunith

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