[RESOLVED] How to display ToolBar by Worksheet name

[yossibnv]

Hello members,
This problem is realy annoying me : I have a VB macro which is triggered by a toolbar icon which created by the macro , the problem here is that i need this icon to be shown (visible) only if the opened excel included work sheet with a specific name suppose "MyMacro" .the problem is that i cant get the name of the opened workbook till i press the icon .
I'll appreciate any solution for that problem.
Thanks In advance
Jozef B.

[Hack]

Moved To Office Development

[westconn1]

load the tool bar icon on workbook open event

[yossibnv]

Thanks Wetconn1 ..., and sorry for the late response(just returned from a vacation) , this sound like a great solution i'll try it and update you back

[yossibnv]

Hell There
I've tried this solution to create toolbar icon from workbook_open event but it didn't worked out, since when this event is triggered the system still doesn't have the active workbook name because it didn't fully opened yet....


Sincerly Jozef..

[westconn1]

i just tested this,

Private Sub Workbook_Open()
For i = 1 To Sheets.Count
MsgBox Sheets(i).Name
Next
End Sub

worked as expected, so what you want should work, do you get an error or what?

[yossibnv]

Thanks Again I'll try this solution and response to you with the results, I appreciate it..

[yossibnv]

Hello westconn1 and thanks again for your efforts to solve this problem ,
let me explain the problem in more details :
I have a macro : "Test.xla"
this macro has 3 sheets "Test1","Test2","Test3" which is not important but created during saving the macro file , there is also part of the macro functions(modules) which is responsible for creating the icon i had mentioned above ,and of course executing the icon functions as required.
when i open an excel file and asks about "sheets(1-3).name" as you described above i get sheets "Test1-Test3" of the current xla file but the whole problem is to know the sheet name of the opened excel file or any other excel file and to decide by its sheets name or workbook's name if to display or not display the icon , but I can't get the name of the opened excel sheets only the xla sheets which is not important.
Thanks again , I hope this issue is much clear now , if not i will be happy to eleborate much more ....

Sincerely Jozef

[westconn1]

ok, i thought the code would be in the workbook that contained the sheets, try specifying the workbook you want the sheets of

for i = 1 to workbooks("theworkbookiwant.xls").sheets.count
     msgbox workbooks("theworkbookiwant.xls").sheets(i).name
next

[yossibnv]

That exactly the problem , that I can't put the code in the desired workbook since I don't know which workbook the user will open , the code is located inside my macro at workbook_open event of an xla file "Test.XLA" and its suppose to show the icon depends on the workbook name that the user is opening . when user open an excel file then my xla macro file (Add-In) is loaded first and at this point it cant know what the user workbook name .
sorry for not making my self clear but i am trying as hard as possible and i really appreciate all your responses , I guess finally we will found solution..

[westconn1]

try moving the code to the workbook activate event

[yossibnv]

OK , Thats sound interesting...
Thnks , Jozef

[westconn1]

on thinking more about this, when the user opens an new workbook the current workbook (i think your macro file) would be deactivated, maybe code could go in the deactivate event for your macro file workbook, to loop through open workbooks to select the sheets as required

[yossibnv]

Hello Westconn1
---------------
Sorry for the late reply , but i am not available to the email acount only at work time, I couldn't do it with work_book activate event , but your last reply seems very close to a solution , I need to deactivate the current work book and to hope that the loop will find the last active work book which the user opened lastly- and is last at the workbooks openning sequel
I will try it , but thats sounds like a brilliant move..
Sincerely Jozef

[yossibnv]

Hello again westconn1 - here is my finding:I've tested the code on workbook_deactivate event and nothing happened when i asked for workbooks names , as matter of fact i tested different workbooks events and the event workbook_beforeclose could control other workbooks and give all the names but the timing of the event is when i close the workbook and its to late.

[westconn1]

can you post a sample workbook that you are working with?

[yossibnv]

Sorry Westconn1 , I am new to this forum i couldn't find how to attach files , but i'll to write here the relevant part from the macro code so there no need for all the code :
at the macro file suppose its name "test.xla" i wrote the following code under "This WorkBook" module :
Private Sub Workbook_Open()
For i = 1 To workbooks.Count
MsgBox workbook(i).name
End Sub
now suppose i am openning an excel file : "Employee.xls"
the macro return the name "Personal.xls" and not "Employee.xls"
The openning order is : 1) "Test.xla" , 2) "Personals.xls" , 3) " "Employee.xls"
but i need the "Employee.xls" and the macro can't return this name .
I found something very interesting : If i put the code under "Personals.xls" and not in my macro file "Test.xla" then when I run the procedure i receive all the work books name ??? - this a by pass solution but i cant use it since i don't want my macro to run from personals, if we could change the openning sequel it might be solved.
Thanks for your efforts , and patience i really appreciate it.
Sincerely Jozef

[yossibnv]

Sorry for miss typing - of course there is NEXT I at the end of FOR

[westconn1]

to attach files you have to "Go Advanced" > manage attachments

this works for me in a std workbook, when i open another workbook it runs

Private Sub Workbook_Deactivate()
For Each w In Workbooks
    If Not w.Name = ThisWorkbook.Name Then
        For Each s In w.Sheets
            MsgBox s.Name
        Next
    End If
Next
End Sub

edit: as an .xla is not visible on screen it is quite likely it has no deactivate event, possibly the only way to do what you want is to use some form of timer to check if an additional work book is open

[yossibnv]

Hello Westconn1,I put your code above in the personals.xls under "ThisWorkbook" module file and it works fine and find all the requested sheets name , but as you mentioned there is problem with xla fie (its workbook is shown in project explorer list but can not use deactivate event) . you also mentioned something about using the timer function , could you please tell me much more details about it : like where in the code/modules to put it and how much delay to set....
Thanks for the tip about attaching files but I guess we will not need it since now we knows were the problem is and we just look for proper solution..
P.S: I am just wondering if I am the only one who encountered this problem or other people too ...
Finally Thanks again your are being very helpfull

[yossibnv]

Hello Westconn1,
I jut put your code above in the personals.xls under "ThisWorkbook" module and it works fine and find all the requested sheets name , but as you mentioned there is problem with xla fie (its workbook is shown in project explorer list but can not use deactivate event) . you also mentioned something about using the timer function , could you please tell me much more details about it : like where in the code/modules to put it and how much delay to set....
Thanks for the tip about attaching files but I guess we will not need it since now we knows were the problem is and we just look for a proper solution..
P.S: I am just wondering if I am the only one who encountered this problem or other people too ,since it seems like a very basic request.
Finally Thanks again - your are being very helpfull

[westconn1]

there is no timer control available to use in vba, so you would have to either use API timers or application.ontime to work as a timer

another possible option is to use your addin to replace the fileopen code on the menu to provide the functionality you want, whenever someone clicks fileopen while your addin is running

see if any of the example here can help, i am sure better can be found, on doing more searching http://www.cpearson.com/excel/CreateAddIn.aspx

[westconn1]

ok if you add a class module to your .xla and add application events, you can then get the event of any workbook in the appliaction
i tested this in a .xla
class module class1

Public WithEvents App As Application
 
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
MsgBox Wb.Name
End Sub

in thisworkbook code module for the .xla

Dim X As New Class1
 
Sub InitializeApp()
    Set X.App = Application
End Sub

you can move the code set x.app = application into workbook open event of the .xla for it to run automatically

there is also a workbook open event that may be better for your purpose

[yossibnv]

Thanks A Lot Westconn1 , I need time to process all this precious info , since its new to me and I am studying a lot here.
thats why I am delaying with the results , but I am happy i have this opportunity to study new issues in VB .

[yossibnv]

Thanks Again Westconn1 for the information and let me share you with my solution, thanks to you.
Here is what I did: I put the following code under class1 module on xla file:

1. Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
2. Dim ShName,Max
3. Max = Workbooks.Count
4. ShName = (Workbooks(Max).Worksheets(1).Name)
5. If ShName = "MySheet" Then
6. ToolBarAdd
7. Else
ToolBarHide
8. End If
9. End Sub

And its doing the Job !!

now I get the relevant workbook and workheet name since he is max and i can trigger it now due to application events, but I have to write all the functions under Class1 module and I was just wondering if I can use the x variable which I defined as an instance of ApplicationEvents but is located at "workkbook_open" event as you told me ,when I've tried to use it to ask for x.workbook name then the whole problem starts again and I get the "personals.xls" workbook but anyway I am very thankfull
for your patience and efforts and feel very lucky to have your assistance which led me to the solution, finally.

Sincerely Jozef

[westconn1]

app also has a sheet activate event, as you want the tooolbar to show based on sheet name you could use this event

Private Sub App_SheetActivate(ByVal Sh As Object)
if sh.name = "somesheet" then
     'toolbar visible code
  else
     ' toolbar not visible code
end if
End Sub

you also need to consider if other workbooks are opened then the workbook you are activating may no longer be max index

[yossibnv]

Sure, but now the customer asks to identify the icon by workbookname so I put the code on App_WorkbookActivate without using Max and its working fine
I feel very lucky to find this forum and this specific thread and its experts....
I am new to vb programming and i am amazed by its power and functionallity ,
and I am very eager to learn more new issues(Will be very happy for any updates on VB issues ) , and till then
Have a nice Day and Thanks for the guide
Sincerely Jozef

[westconn1]

if it is all working now, please mark thread resolved

[yossibnv]

Done with pleasure !!