Hi Daniel
1. You can run a macro automatically when you open a workbook if you call it "Auto-Open". Auto-Open has to be in a standard module, not in the workbook.
2. Your Sub Test_For_OPEN contains errors. If you put your cursor anywhere inside the macro and hit function key F5 it will run and the IDE will then flag the errors. After fixing you can then test it by re-opening the file.
3. In any case your macro is over-engineered. I think this is what you are trying to do :-
Sub Auto_Open()
If MsgBox("Do you want to automatically start the weekly process ?", vbYesNo, "Auto Start of Weekly KPI process") = vbYes Then
ConstructKPIreport
Else
MsgBox "Automatic KPI Report will be bypassed"
End If
End Sub
Sub ConstructKPIreport()
' code goes here
End Sub
4. Try this :- MsgBox "Warning" & vbCr & "Automatic KPI Report will be bypassed"
' (vbCr is a VB built in constant for Char(13) , MsgBox does not need vbCrLf )
5. On a point of style
If (SOpt_Select = "Y" Or SOpt_Select = "y") Then
should be
If (SOpt_Select = "Y") Or (SOpt_Select = "y") Then
you don't actually need any brackets at all but if you must use them then do it to isolate the two conditions you are testing, as in my example
this is slightly better :-
If UCase(SOpt_Select = "Y") Then
Apologies if my post contains A with circumflex. It's a Yahoo bug. . . Yahoo Groups does not like multiple spaces or indents.
Regards
Derek +++
>________________________________
> From: David Grugeon <yahoo@grugeon.com.au>
>To: ExcelVBA@yahoogroups.com
>Sent: Monday, 4 June 2012, 22:40
>Subject: RE: [ExcelVBA] Re: Need to automatically invoke an EXCEL MACRO
>
>
>
>Hi Daniel
>
>Try opening the workbook from Excel (rather than making the workbook open directly and bring excel along with it. I have a feeling the debug stuff does not always get operational while Excel is not fully open. It should work OK apart from the debugging.
>
>Make sure the macro is in the "This workbook" module, not in a standard (added) module. All event modules need to be in the object they are triggered by.
>
>Make sure you do not have security settings that will prevent macros from running on startup.
>
>Select Case Choice
>Should probably be
>SstrName = Choice
>It does not make sense to have A Select Case statement which is not immediately followed by a Case = statement. In fact I don't think it is allowed and may cause the sub to terminate.
>
>I also think that the sub will always go into an unending loop but I have not checked this in detail.
>
>Best Regards
>David Grugeon
>Excel VBA Group Moderator
>
>-----Original Message-----
>From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of danielrose02
>Sent: Tuesday, 5 June 2012 6:09 AM
>To: ExcelVBA@yahoogroups.com
>Subject: [ExcelVBA] Re: Need to automatically invoke an EXCEL MACRO
>
>Unfortunately, this approach DID NOT WORK.
>
>Here is my MACRO:
>Private Sub Workbook_Open()
>Call Test_For_OPEN
>End Sub
>
>and
>
>Test_For_OPEN is:
>
>Sub Test_For_OPEN()
>Dim Choice As String
>Dim SstrName As String, Sloop_control As String, Sselect_choice As String
>Dim SOpt_1 As String, SOpt_2 As String, SOpt_3 As String, SOpt_4 As String, SOpt_5 As String
>Dim SPath_Name As String, SWorkBook_Name As String
>Dim SOpt_Select As String
>Sloop_control = "N"
>Choice = "5"
>' Choice = "1"
>Select Case Choice
>Do
>SOpt_1 = "Do you want to automatically start the weekly process?"
>SOpt_4 = "Enter either:" & Chr(13) & Chr(10) & "Y to start" & Chr(13) & Chr(10) & "N to Stop"
>SstrName = Application.InputBox(Prompt:=SOpt_1 _
>& Chr(13) & Chr(10) & SOpt_4, _
>Title:="Auto Start of Weekly KPI process", _
>Default:="Y", Type:=2)
>If SstrName = vbNullString Then
>Exit Do
>Else
>Select Case SstrName
>Case "Y", "y"
>SOpt_Select = "Y"
>Sloop_control = "Y"
>Case "N", "n"
>SOpt_Select = "N"
>Sloop_control = "Y"
>Case Else
>SOpt_Select = vbNullString
>End Select
>' Something to loop
>If SOpt_Select = vbNullString Then
>Sloop_control = "N"
>End If
>End If
>Loop Until (Sloop_control = "Y" Or Sloop_control = "y")
>If (SOpt_Select = "Y" Or SOpt_Select = "y") Then
>Call A_Construct_KPI_REPORT
>Else
>MsgBox "Automatic KPI Report will be bypassed"
>End If
>End Sub
>
>I tried putting breakpoints in Test_For_OPEN and it seems that the VBA debugger does not even get control at this point.
>
>--- In ExcelVBA@yahoogroups.com, "Hafizullah" <hafizullah@...> wrote:
>>
>> A better strategy will be to have the macro run as a Workbook_Open() event.
>>
>> So the following macro will be a property of the Workbook:
>>
>> Private Sub Workbook_Open()
>> DoStuff
>> End Sub
>>
>>
>>
>> You won’t be able to automate this through a batch file, and doing it via automation in VBS is a can of worms that I guarantee you don’t want to get into if you don’t already know a lot about VB/A and VBS.
>>
>>
>>
>> Hafizullah
>> Hafizullah
>> @}->-`,--
>>
>>
>>
>>
>>
>>
>>
>> [Non-text portions of this message have been removed]
>>
>
>------------------------------------
>
>----------------------------------
>Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
>
>----------------------------------
>Visit our ExcelVBA group home page for more info and support files:
>http://groups.yahoo.com/group/ExcelVBA
>
>----------------------------------
>More free tutorials and resources available at:
>http://www.mousetrax.com
>
>----------------------------------Yahoo! Groups Links
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar