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?

 

[DominicB]

Hi slimimi

Sorry for not replying sooner - have been really busy these last few days.

The code (if I remember rightly) needs to go into your userform module, but as it stands it won't be linked to a button. There are two modules, one is a function which won't do anything except return a result when requested (in this instance the path of a user's Desktop). There is a module which won't do anything until called by name (which at the moment is "SaveAndClose".

If you wanted to link the "SaveAndClose" module to CommandButton1 you would have to change :

Sub SaveAndClose()

to

Sub CommandButton1()

Unfortunately, if you change the name of a sheet or range addresses, VBA doesn't react as formulae do and change them automatically. However, if you rename "Sheet1", you can always use find and replace to change all instances.

HTH

DominicB

 

[slimimi]

Hi Dominic - sorry to be a pain. I didnt mean to push you earlier - was just worried that you might not have received my post Hope all is well with you.

Unfortunately i still cant get the code to work.
I am sure i am doing something really silly.

I pasted this code in the Userform Initialize

Private Sub UserForm_Initialize()
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

Then i have gone to the command button on this userform called cmdSAVElog and pasted the 2nd part of the code there

Private Sub cmdSAVElog_Click()


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

I still have 2 problems.

The first is an error which says "Only comments can appear after End Function, etc" and then it highlights this line :

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

The 2nd problem is with the 2nd code i am getting this error when i try to click on my save button (while running code).

It says :

Compile error - Sub or Function not defined and then it highlights the following lines:

Private Sub cmdSAVElog_Click()
On Error Resume Next
MkDir GetSpecialfolder(CSIDL_DESKTOP) & "\Trade Logs"

Then regarding the OTHER code you gave me to intercept the CLOSE, SAVE and SAVEAS commands on excel. These worked fine but it stopped my MODIFY TEMPLATE SAVE procedure (remember the one where i had to type a password). Here is the entire code for this 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
End If


'Sound
Call sndPlaySound32("C:\documents and settings\" & Environ("USERNAME") & "\desktop\Trade Logs\Sounds\close.WAV", 0)



'Unloads Both Userforms
Unload Me
Unload frmSAVElog

'Opens SaveAs Dialog Box
Application.Dialogs(xlDialogSaveAs).Show

End Sub

I remember that you told me to insert a line before this code to allow this procedure to still work (even though we have intercepted the SAVE, CLOSE and SAVEAS commands on excel).

This done the trick!!! but it caused a further issue.
The issue is this:

We have intercepted CLOSE, SAVE and SAVEAS commands.
We have now allowed the MODIFY TEMPLATE SAVE with Password Checker

BUT when we reopen the workbook - the CLOSE, SAVE and SAVEAS intercepts have now gone.

Oh dominic - i am so sorry to trouble you with all this.
I just would really like to get it all sorted if possible.

Hope you wouldn't mind to help me with this last thing.
Thanks in advance.

Have a great weekend.

SLIMIMI