Code to position toolbar PLEASE help

[slimimi]

Hi there - using excel 2003.

I have the following code to display a toolbar everytime my template is open

'Show Custom Toolbar "Trade Log" on Workbook Open
On Error Resume Next
With Application.CommandBars("Trade Log")
.Position = msoBarFloating
.Left = 5000
.Top = 600
.Visible = True
End With
End Sub

(hope i wrapped the code quotes correctly this time ))))

I noticed that when you drag a custom toolbar to the far right of your screen - it locks in to a vertical toolbar space.

How can i position my toolbar in this place automatically by modifying the above code please?

 

[slimimi]

Hi Dominic - i got it working.
I created a module within my template.
I pasted the following code in there :

Sub TradeLogTB()
frmSAVElog.Show
End Sub

Then clicked on the button and selected TradeLogTB

This worked great but i have another problem now - hope you can help me - sorry again...

Macro opens this userform:

Private Sub cmdCANCELsavelog_Click()
'Cancels frmSAVElog
Unload Me
End Sub

Private Sub cmdMODIFYtemplate_Click()
'Opens Password Checker which is frmPasswordSAVE
frmPasswordSAVE.Show
End Sub

the frmPasswordSAVE autoform has the following code assigned to the OK button:

Private Sub cmdPASSok_Click()
'Password Checker
If Me.txtPASSWORD.Value <> "hundred" Then
MsgBox "Incorrect Password", vbExclamation
Me.txtPASSWORD.SetFocus
Exit Sub
End If
If Me.txtPASSWORD.Value = "hundred" Then
MsgBox "Correct Password"
End If
Unload Me

End Sub

1. If password is correct - i need to also unload the frmSAVElog userform
2. If password is correct - i would like to be automatically directed to the normal SAVEAS function on excel so that i can save over the template with changes, etc....

(this is why i incorporated a password into this save option).

However,
on the frmSAVElog autoform (which has 3 buttons, SAVE TRADE LOG, MODIFY TEMPLATE (which we have just covered) and CANCEL)

If user clicks on SAVE TRADE LOG - i would like the template to be automatically saved, using a filename from Cell A1 on a hidden sheet3 (if this is possible).

I would like this file to be automatically saved on the users Desktop and in a folder called "Trade Logs". If there is no folder, i would love it if a folder called "Trade Logs" can be created on the desktop of that computer and then the file saved automatically in that folder as a .xls please.

Hope you dont mind helping me with this.

 

[DominicB]

Hi slimimi

OK. Code time again. Not sure what your post means about showing a caption, but I've set the icon and show it with a caption, by adding a couple of lines to it. Have a look at this :

Sub BuildTB()
Dim cb As CommandBar, ctrl As CommandBarControl
Name1 = "My Toolbar"
On Error Resume Next
Application.CommandBars(Name1).Delete
Set cb = Application.CommandBars.Add(Name:=Name1, temporary:=True)
With cb
.Visible = True
.Position = msoBarRight
End With
Application.CommandBars(Name1).Controls(1).ListIndex = 6
Set Btn = cb.Controls.Add(Type:=msoControlButton)
With Btn
.FaceId = 263
.OnAction = "MyMacro1"
.Caption = "My Macro 1"
.Style = msoButtonIconAndCaption
End With
Set Btn = cb.Controls.Add(Type:=msoControlButton)
With Btn
.FaceId = 42
.OnAction = "MyMacro2"
.Caption = "My Macro 2"
.Style = msoButtonIconAndCaption
End With
End Sub

Sub MyMacro1()
MsgBox "This is MyMacro1"
'UserForm1.Show
End Sub

Sub MyMacro2()
MsgBox "This is MyMacro2"
'UserForm2.Show
End Sub
 
Sub DeleteTB()
On Error Resume Next
Application.CommandBars("My Toolbar").Delete
On Error GoTo 0
End Sub

There's also a new routine there that will delete the toolbar - and that's what we'll use to remove the toolbar when the workbook is deactivated or closed. To handle this we will make use of the workbook events. Paste this code into the ThisWorkbook module of your workbook :

Private Sub Workbook_Activate()
Call BuildTB
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call BuildTB
End Sub
 
Private Sub Workbook_Deactivate()
Call DeleteTB
End Sub
 
Private Sub Workbook_Open()
Call DeleteTB
End Sub

HTH

DominicB

 

[DominicB]

Hi slimimi

Whoooooaaaa! Can we just go for one question at a time here

1. If password is correct - i need to also unload the frmSAVElog userform
2. If password is correct - i would like to be automatically directed to the normal SAVEAS function on excel so that i can save over the template with changes, etc....

OK. Just use something like this to close the other open UserForm (we have got two open at once here haven't we ... ?)
This closes your userform :

Unload frmSAVElog

and this should open the Save As dialog box :

Application.Dialogs(xlDialogSaveAs).Show

OK. Assuming that works, what needs to happen next?
BTW, may have to stop posting for the day soon. Still post back, and if nobody else answers the question I'll carry on helping tomorrow.

HTH

DominicB

 

[slimimi]

That works wonderfully. And i can see the advantages of doing it this way as oppose to creating a custom toolbar without code.
Thank you.
I just need to get the buttons to run now.

Can i just tell you the procedure please?

User clicks on (save trade log) custom command bar button
Userform pops up with 3 options : SAVE : MODIFY : CANCEL

If click CANCEL - unload form (i got that working hehe)

If click MODIFY - 2nd userform pops up asking for password
If password is wrong - msgbox "incorrect password" (got this working)
If password is right (i dont have this yet) :

***need to be directed to normal SAVE AS excel box whereby i can save over template, and change filename and location, etc... this is mainly for me to use....

If click SAVE (i dont have this yet) :

***need an autosave to happen whereby the following procedure happens:

takes filename from Sheet3 : Cell (A1) ..... Sheet3 is a hidden sheet (just to let you know)

saves workbook on Users Desktop under foldername "Trade Logs"
if no "Trade Logs" then create folder called "Trade Logs" and save file there
Once save complete - close active workbook.

do you think this is possible?

 

[slimimi]

No worries dominic - you have been WONDERFUL - i dont know how else to thank you... Perhaps we can continue tomorrow. There is just a few more things i cant get my head round - but after reading your posts - i am certainly learning fast and understanding a little bit more each time... Thank you for sharing your wonderful talents with me... Knowledge is the best gift in the world....

 

[slimimi]

Yes you are correct. 2 userforms.

The 2nd userform comes up when i click on MODIFY TEMPLATE which is a command button option no the 1st Userform.
This 2nd userform is just to confirm password so that i can have the ability to SAVE OVER this template.

I don't want users to have this option though.
I just want that, when they click on SAVE on the 1st Userform, they are autosaved with a filename taken from (hidden Sheet3) : Cell A1 and then closed out of the spreadsheet (but not excel)...

 

[DominicB]

Hi slimimi

OK. The code below will create a folder on the local desktop (if one doesn't already exist) and will save the workbook using the filename in cell A1 on Sheet3 (it doesn't matter that this sheet is hidden) and will then close the workbook. If the file is to be overwritten a warning will not be shown.

This code might seem more complicated than it needs to be : this is because finding the users desktop directory isn't that easy. I've used some code of Chip Pearson's to get the job done. The macro SaveAndClose is what does the job, but you'll need the rest of the code in in your userform module somewhere :

Const CSIDL_DESKTOP = &H0
Const MAX_PATH = 255
Private Type ****EMID
    cb As Long
    abID As Byte
    End Type
    Private Type ITEMIDLIST
    mkid As ****EMID
    End Type
Public Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" (ByVal hwndOwner As Long, ByVal nFolder As Long, pidl As ITEMIDLIST) As Long
Public Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
 
Public Function GetSpecialfolder(CSIDL As Long) As String
Dim Res As Long
Dim IDL As ITEMIDLIST
Dim Path As String
Res = SHGetSpecialFolderLocation(100, CSIDL, IDL)
If Res = 0 Then
Path = Space(512)
Res = SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal Path$)
GetSpecialfolder = Left$(Path, InStr(Path, Chr$(0)) - 1)
Exit Function
End If
GetSpecialfolder = ""
End Function
 
Sub SaveAndClose()
On Error Resume Next
MkDir GetSpecialfolder(CSIDL_DESKTOP) & "\Trade Logs"
On Error GoTo 0
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=GetSpecialfolder(CSIDL_DESKTOP) & "\Trade Logs\" & Sheets("Sheet3").Range("A1").Value
ActiveWorkbook.Close
End Sub

HTH

DominicB

 

[slimimi]

Wow Dominic - you are very kind - thank you so much...
I cant wait to try the code - will be doing so in an hour from now after settling the kids (ha ha).

About the workbook closing when the user has saved, if i didnt want this to happen - would i just simply delete the line at the end of your code?

Also, once again dominic, i think i am going to struggle as to where to put this code (ha ha).

Basically the user clicks on a Custom Command button called Save Trade Log. This then opens up userform with 3 Command Button Options. If they click SAVE - then the code you have posted for me - is what is supposed to happen. Hope that makes sense to you.

Last thing now please is would it be possible for me to disable the SAVE and SAVEAS and X (close) options on this Template ONLY so that the user has no choice but to Use the Custom Command Bar Button called "Save Trade Log" to save their new workbook?

I was thinking something like follows:

If user clicks SAVE or SAVEAS or CTRL+S = Msgbox "Please use the Save Trade Log Button provided at the bottom of the screen)


If user clicks X to close Template Window = Msgbox "Please save your work before you close this Template".

I am a bit confused on the 2nd one. Reason being is that when you have made changes to a doc and X - you are prompted with SAVE options box in excel. However - if you have NOT made changes to doc and then X - it just closes. So basically i wondered if it can be done this way so that the X is not TOTALLY disabled from the spreadsheet. Otherwise user will always have to close their Trade Log when they want to do a save.

Hope this makes sense.
Thanks in advance.
I wish i knew how to repay you for all the help you have given to me

 

[DominicB]

Hi slimimi

To answer your question, yes, it's just one line that handles the workbook close command - remove it and it won't close. If you have any problems where to put your code, say so and I'll try and help.

As regards disabling Save / Save As, the easiest way to handle this is not disable the close or save buttons (if you go this route there's keyboard shortcuts and menu structures to worry about as well as the toolbar buttons) but just intercept any calls to either of them. This code will handle this for you (this must go in your ThisWorkbook module :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Please save your work before you close this Template"
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "Please use the Save Trade Log Button (provided at the bottom of the screen)."
End Sub

However, this will probably stop your save routine working that we looked at earlier. To make this work, you will need to add this line before the ActiveWorkbook.SaveAs Filename etc command :

Application.EnableEvents = False

This will stop all event code from running (which would normally stop your save from taking place).

Have a nice evening! (it's just approaching dinner time here). Where are you BTW ... ?

HTH

DominicB

 

[slimimi]

Wow- thank you dominic. The kids are off with the wife now so i am gonna settle down to implement all your kind work. Hope you have a nice dinner.

Yes - i don't think i know where to put the 1st code that you sent me. If you dont mind letting me know.

Where am i? Well - its a long story (ha ha).. I am originally from london, uk but have been living out in Brunei for the past 8 years. Got married over here to my lovely wife and now have 3 beautiful children (which is why i haven't gone back to london) ha ha....

Hope you know where brunei is - alot of people dont. Its just about an hours flight away from Singapore.

Take care and will feedback to you as soon as i've implemented the code.

Thanks again.
Oh yeah - its just where to paste the 1st code you sent please.
Thanks again.