Auto_Open

sgmpatnaik · Apr 25, 2012

Hello Good Evening

i Create one Aut

pen Macro and it's working when i am opening the Excel Work Book

But i want to create some more Aut

pen Macros but i can't

please help me

Hui · Apr 25, 2012

Sgmpatnaik

You might want to try explaining what you want to do?

Is it in the same workbook or a different workbook?

Can you post a sample file? Read the 3 Green Sticky posts for instructions.

sgmpatnaik · Apr 25, 2012

Yes Sir it is in same work book and i give two links for your kind information and please suggest me

please

http://dl.dropbox.com/u/75654703/Toshali%20Cements%20Accounts.xls

and

http://dl.dropbox.com/u/75654703/index.xls

Hui · Apr 25, 2012

What do you want done with the Cements Accounts workbook?

If you add the code below to the Index.xls files ThisWorkbook object in VBA

It will animate a message

[pre]

Code:

Private Sub Workbook_Open()
Dim i As Integer, Repeat As Integer
Dim myWait As Double
Dim myMessage As String

myMessage = &#34;Welcome to Enterprises&#34;
myWait = 1 &#39;How long a wait?
Repeat = 1 &#39;How many times to repeat

Sheets(&#34;Index&#34;).Select
For j = 1 To Repeat
For i = 2 To 16
Cells(2, i).Value = myMessage

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + myWait
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Cells(2, i).Value = &#34;&#34;
Next i
For i = 16 To 2 Step -1
Cells(2, i).Value = myMessage
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + myWait
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Cells(2, i).Value = &#34;&#34;
Next i
Next j
End Sub

[/pre]

sgmpatnaik · Apr 26, 2012

Thank Q Sir,

it's working

and one more request

when i will share my workbook it will take too slow to open in other system and some times in my system also is there any thing wrong with me

SirJB7 · Apr 26, 2012

Hi, sgmpatnaik!

I'd bet there is something wrong with you...

joke

But how to discover it! I don't know what does the workbook do (and I cant analyze it now), but I opened both uploaded files almost instantly, so there might have been environment or equipment issues and not necessarily related to this file.

Regards!

PS: joke?... hmmm...

PS2: yes, joke

sgmpatnaik · Apr 26, 2012

Hello Sir,

Thanks for your Joke!!

As per your Codes i did, but there is one problem that is

I Cannot open the another Work Book until the program will end, but i want to continue scroll the msg until my Workbook is close and i can open another work books for doing works

Note:"I have no that much of idea in English so kindly adjust with my language"

[pre]

Code:

Sub Workbook_Open()
Dim sh As Object
Dim arrSheetNames As Variant
&#39;--
&#39;Populate sheet array with names of sheets
&#39;that must be always visible.
&#39;Note: Customize sheet names.
arrSheetNames = Array(&#34;Sheet1&#34;)
&#39;Loop in Activesheets in workbook
&#39;Dim sp As String
Dim sTxt As String
Dim x As Integer, y As Integer, Repeat As Integer
Dim Start, delay
With ActiveWorkbook.Worksheets(&#34;Sheet1&#34;)
.Visible = True
sTxt = &#34;Welcome !!&#34;
MsgBox &#34;Welcome&#34;
Repeat = 1 &#39;How Many Times to Repeat
Sheets(&#34;Sheet1&#34;).Select
For y = 1 To 25                                         &#39;15 Loops through the scrolling
For x = 1 To 50                                           &#39;Index number of times
Start = Timer                                         &#39;Set start to internal timer
delay = Start + 0.15                                  &#39;Set delay for .15 secs
Do While Timer &#60; delay                                &#39;Do the display routine
[B1] = Space(x) & sTxt                            &#39;Show 1 str @ a time
DoEvents                                          &#39;do there things
Loop                                                  &#39;Loop until delay is up
DoEvents
Start = Timer                                         &#39;and reset the timer
delay = Start + 0.15                                  &#39;and the delay
Next x                                                    &#39;Show the next str
For x = 50 To 1 Step -1
Start = Timer                                         &#39;Set start to internal timer
delay = Start + 0.15                                  &#39;Set delay for .15 secs
Do While Timer &#60; delay                                &#39;Do the display routine
[B1] = Space(x) & sTxt                            &#39;Show 1 str @ a time
DoEvents                                          &#39;do there things
Loop                                                  &#39;Loop until delay is up
DoEvents
Start = Timer                                         &#39;and reset the timer
delay = Start + 0.15                                  &#39;and the delay
Next x                                                    &#39;Show the next str
Next y                                                        &#39;Do this again - 15
[B1] = &#34;&#34;                                                     &#39;Reset

End With

End Sub

[/pre]

sgmpatnaik · Apr 26, 2012

Sir,

Kindly replay

hence i oblige

SirJB7 · Apr 26, 2012

Hi, sgmpatnaik!

Two questions:

a) how do you know that the another workbook ended its job?

b) do you open it by code or manually?

Regards!

PS: don't worry about your english, it isn't the native language for many other people too

Luke M · Apr 26, 2012

If you're going to be doing "anything" such as editing cells in original workbook, or opening another workbook, you might want to use the application.ontime method rather than using a looping timer.

sgmpatnaik · Apr 27, 2012

Thank Q Sir,

i have some excel work books for my personal and office use also for Ex. Staff Attd., accounting, consignment note like this type

But i mention the above codes in Accounting Books only and the restbooks are free with macro and VBA, i mean the rest books are normal type

When i open the Accounting Workbook on time of scrolling msg i can't open the next books as like staff attd., and more and new excel file also

But small request sir i want to scroll the msg up to my accounting book is closed and i can open all excel files when this file is open

please hlep

hence i oblige

sgmpatnaik · Apr 27, 2012

sir please replay

i am waiting

Luke M · Apr 27, 2012

sgmpatnaik,

Please realize that many of us live in different time zones, and so are not always online. Also, this is a volunteer based forum, and none of us are forced to work here. Please read:

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

As I stated before, since you are "interrupting" the macro by opening other workbooks and presumably doing something in them, you'll need to use the Application.OnTime method rather than a loop. For more info see these:

http://www.ozgrid.com/Excel/run-macro-on-time.htm

http://www.cpearson.com/excel/OnTime.aspx

SirJB7 · Apr 27, 2012

Hi, sgmpatnaik!

I absolutely agree with Luke M, both regarding time zones and sources for your issue.

Besides, if you want to upload a zip (.rar, .zip, compressed) file with your files, it'd be useful for those who intend to help you.

Regards!

Montrey · Apr 27, 2012

hahaha!

"I am WAITING!!"

sgmpatnaik · Apr 29, 2012

Hello Good Morning

Sir i have one doubt

Well i am using in excel 97 to 2003 there is one problem when i am using a big formula in excel then it is not excepted but when i use the same formula in excel 2007 it is accepted and i can do my work

Kindly help me about this problem

With regards

Patnaik

NARAYANK991 · Apr 29, 2012

Hi Patnaik ,

There are several differences between Excel 2007 and its earlier versions ; generally , there are workarounds for the Excel 2007 formulas which do not work in earlier versions.

If you post examples of these formulas from your workbook(s) , you are sure to get help.

Narayan

sgmpatnaik · Apr 29, 2012

=IF(C2:C1504=1,"Party Name",IF(C2:C1504=2,"Party Name",IF(C2:C1504=3,"Party Name",IF(C2:C1504=4,"Party Name",IF(C2:C1504=5,"Party Name",IF(C2:C1504=0,"-",IF(C2:C1504>5,"Party Not Created")))))))

when i am using the above formula in excel 97 to 2003 from the party code 1 to 100 and more it is not accepted but when i am using the same formula it will work

pls suggest

Hui · Apr 29, 2012

Sgmpatnaik

That looks like an Array Formula

Are you entering it with Ctrl Shift Enter ?

sgmpatnaik · Apr 29, 2012

No sir i have no idea about the Array Formula and i have no idea about the sort cut Ctrl Shift Enter

and i am entering that formula direct in excel file

Hui · Apr 29, 2012

Are you using the formula in Row 2:1504 ?

Have you tried in Row 2 putting:

Code:

=IF(C2=1,&#34;Party Name&#34;,IF(C2=2,&#34;Party Name&#34;,IF(C2=3,&#34;Party Name&#34;,IF(C2=4,&#34;Party Name&#34;,IF(C2=5,&#34;Party Name&#34;,IF(C2=0,&#34;-&#34;,IF(C2&#62;5,&#34;Party Not Created&#34;)))))))

then copying down

sgmpatnaik · Apr 29, 2012

yes exactly

Hui · Apr 29, 2012

It works fine for me in Excel 2010?

Do you have Calculation set to Automatic ?

Goto Formulas, Calculation options and check

Can you email it to me?

(Click on Excel Ninja, Email address is at the bottom of the page)

sgmpatnaik · Apr 29, 2012

yes sir it is in Automatic mode and i can't send the file now due now i am chatting from another system

pls mail me a test mail to my mail id and i will forward the my excel book to your mail id

with regards

patnaik

sgmpatnaik · Apr 29, 2012

sorry sir i forget to give my mail id here is that

sgmpatnaik@gmail.com

Auto_Open

Active Member

Excel Ninja

Active Member

Excel Ninja

Active Member

Excel Rōnin

Active Member

Active Member

Excel Rōnin

Excel Ninja

Active Member

Active Member

Excel Ninja

Excel Rōnin

Member

Active Member

Excel Ninja

Active Member

Excel Ninja

Active Member

Excel Ninja

Active Member

Excel Ninja

Active Member

Active Member