Solved: Problem in personal worksheet

[Shanmugam]

Hi,

I am having problem in coding through personal workbook.

My task is -- Whenever i open the any workbook name starts with "Student", then worksheet name should change from "Sheet1" to "Count". For which i stored my macros in personal.xls file, i.e. (C:\Program Files\Microsoft Office\OFFICE11\XLSTART\personal.xls)

E.g. If i open the workbook and the name starts with "Student" (actual file name is "Student_05052009.xls"), then my macro should run and change the worksheet name from "Sheet1" to "Count". Then file should save as the file (correct worksheet file) in different/selected location (i.e. save as dialog box).

Below is the code i tried to do this:

Private Sub Workbook_open()
If Left(ActiveWorkbook.Name, 7) = "Student" Then
Call Student_report
Else: Exit Sub
End If
End Sub

Sub Student_report()
Application.Screenupdating = False
ActiveSheet.Name = "Count"
Application.Dialogs(xlDialogSaveAs).Show
End Sub

But with the above code i am getting error msg as well as the worksheet renaming only to personal.xls and not student_05052009.xls file and also it is not storing in proper location (i.e. save as dialog box is not working properly). Can you please help me to correct this code?

Thanks in anticipation,
Shanmugam

[mdmackillop]

Give some time for the workbook to open.

In ThisWorkbook module

Private Sub Workbook_open()
Application.OnTime Now + TimeValue("00:00:05"), "Student_report"
End Sub


in standard module

Sub Student_report()
If Left(ActiveWorkbook.name, 7) = "Student" Then
ActiveSheet.name = "Count"
Application.Dialogs(xlDialogSaveAs).Show
End If
End Sub

[Shanmugam]

Mac,

i am still getting the same problem, i.e. activesheet.name changing only on personal.xls file and not in "student" file.

[mdmackillop]

Try
ActiveWorkbook.Sheets(1).Name = "Count"

[Shanmugam]

Mac,

Without your latest correction (i.e. without 'ActiveWorkbook.Sheets(1).Name = "Count"') its working fine now.

The mistake what i did is, i pasted all the code in "This Worksheet". Now splitted the codes seperately in "Modules" and now its working fine.

Thanks a ton for this,

Shanmugam

[Shanmugam]

Mac,

I am getting problem here. When i have one or more than one files opened, this macro is not working. But if i have this file alone opened, macro is working fine. Can you please help me in this?

[GTO]

Greetings,

I have never used PERSONAL.xls, so could well be off. I did create one, and thought maybe?

In Personal.xls:

In ThisWorkbook Module:

Option Explicit

Dim X As New clsAppEvents

Private Sub Workbook_Open()
Set X.XL_App = Application
End Sub


In a newly created ClassModule:

Rename the Class "clsAppEvents"
paste:
Option Explicit

Public WithEvents XL_App As Application
'

Private Sub XL_App_WorkbookOpen(ByVal Wb As Workbook)
If Left(Wb.Name, 7) = "Student" Then
If Not ShName_Exists(Wb, "Count") Then
Wb.ActiveSheet.Name = "Count"
Application.Dialogs(xlDialogSaveAs).Show
Else
Wb.Worksheets("Count").Select
Application.Dialogs(xlDialogSaveAs).Show
End If
End If
End Sub

Private Function ShName_Exists(Wb As Workbook, ShName As String) As Boolean
Dim Sh
For Each Sh In Wb.Sheets
If Sh.Name = ShName Then ShName_Exists = True: Exit For
Next
End Function


Hopefully that's of help,

Mark

[Shanmugam]

GTO,

I pasted the first set of codes in 'ThisWorkbook' and I created new 'Class' (Instead of Module) and pasted the second set of codes.

I don't know where and how to rename the Class "clsAppEvents" and you explained.

And while i run this application (with this renaming step), i am getting 'Compile-error' and the code gets highlighted in 'X as New clsAppEvents' (in 'ThisWorkbook').

Shan

[Shanmugam]

<re-framing the above sentences>

GTO,

I pasted the first set of codes in 'ThisWorkbook' and I created new 'Class' (Instead of Module) and pasted the second set of codes (in class module).

I don't know where and how to rename the Class "clsAppEvents" as you explained.

And while i run this application (without this renaming step), i am getting 'Compile-error' and the code gets highlighted in 'X as New clsAppEvents' (in 'ThisWorkbook').

Shan

[GTO]

GTO,

I pasted the first set of codes in 'ThisWorkbook' and I created new 'Class' (Instead of Module) and pasted the second set of codes.

I don't know where and how to rename the Class "clsAppEvents" and you explained.

And while i run this application (with this renaming step), i am getting 'Compile-error' and the code gets highlighted in 'X as New clsAppEvents' (in 'ThisWorkbook').

Shan

Use Insert|Class Module from VBIDE's menubar to create a new Class Module. Rename it in the Properties window.

It must be renamed for 'X as New clsAppEvents' to reference correctly.

Hope thia helps,

Mark

[Shanmugam]

I did renaming the class.

But while running the module, i am getting debug in 'Function ShName_Exists' as 'Object variable or with block variable is not set'.

[GTO]

Please post the code exactly as you currently have it, showing under what module you have everything.

Thanks,

Mark

[Shanmugam]

Here is the code:

ThisWorkbook contains:

Option Explicit

Dim X As New clsAppEvents

Private Sub Workbook_Open()
Set X.XL_App = Application
End Sub

In 'Class Module' (after renaming as 'clsAppEvents'):
Option Explicit
Public WithEvents XL_App As Application
Private Sub XL_App_WorkbookOpen(ByVal wb As Workbook)
If Left(wb.Name, 7) = "Student" Then
If Not ShName_Exists(wb, "Count") Then
wb.ActiveSheet.Name = "Count"
Application.Dialogs(xlDialogSaveAs).Show
Else
wb.Worksheets("Chennai Report").Select
Application.Dialogs(xlDialogSaveAs).Show
End If
End if
End Sub

Private Function ShName_Exists(wb As Workbook, ShName As String) As Boolean
Dim Sh
For Each Sh In wb.Sheets
If Sh.Name = ShName Then ShName_Exists = True: Exit For
Next
End Function

While executing, i am getting debug msg as, ''Object variable or with block variable is not set' and my cursor stands in, 'For Each Sh In wb.Sheets'.

Hope this is what you required.

[Aussiebear]

Does the function know what workbook the "For Each Sh In wb.Sheets" is referring to?

[Shanmugam]

I am not sure whether it is referring! I have given all the codes i used (see above).

Shan

[GTO]

Shan,

I would really not like to screw up twice today, but what pray tell is this?

If Left(wb.Name, 7) = "Student" Then
If Not ShName_Exists(wb, "Count") Then
wb.ActiveSheet.Name = "Count"
Application.Dialogs(xlDialogSaveAs).Show
Else
wb.Worksheets("Chennai Report").Select
Application.Dialogs(xlDialogSaveAs).Show
End If
End If


Where it says "Chennai Report", it must say "Count".

See, I just added the test in, in case you opened a workbook (left 7 char = "Student") wherein the sheet "Count" already existed. In that case, 'Count' would be selected, as attempting to name another sheet a name that had already been used would result in an error. Anyways, my bet is you tried this on a wb wherein "Chennai Report" doesn't exist.

Does that help?

Mark

Hi Ted :-)

[Shanmugam]

GTO,

It's a typo mistake from my side. The reason i tried using this code for other worksheets (with similar requirement).

But still i corrected the above, getting same bug. i am driving crazy.

[GTO]

Hi Shan,

Sorry you are having trouble. I just recreated PERSONAL.xls with the same code, made three workbooks (Student_01.xls, New Microsoft Excel Worksheet.xls and Student_01.xls) and opened them in the listed order.

I originally developed in 2003, tonight in 2000.

I am afraid I cannot replicate the error.

Mark

Edit: BTW (and a bit of grasping at straws), are you sure you saved the changes to PERSONAL before closing Excel and restarting?

[Shanmugam]

Yes Mark. i saved this file in personal.xls. Just tried before. still getting the same bug.

I guess in
Dim Sh --
'Sh' is not declared.

Shan