Hi DaveO,
When they close
the workbook I want to re-enable all of the ones I disabled earlier, but
can't see how to do it.
I would use a txt file for this:
Dim oBar As CommandBar
Dim lFile As Long
Dim sFilename As String
Dim bEnabled As Boolean
Dim bVisible As Boolean
sFilename = ThisWorkbook.Path & "\Commandbars.ini"
lFile = FreeFile
Open sFilename For Output As lFile
On Error Resume Next
For Each oBar In Application.CommandBars
bEnabled = oBar.Enabled
bVisible = oBar.Visible
Write #lFile, oBar.Name, bEnabled, bVisible
oBar.Visible = False
oBar.Enabled = False
Next
Close #lFile
Then later on:
Dim oBar As CommandBar
Dim lFile As Long
Dim sFilename As String
Dim sTemp As String
Dim bEnabled As Boolean
Dim bVisible As Boolean
lFile = FreeFile
sFilename = ThisWorkbook.Path & "\Commandbars.ini"
If Dir(sFilename) = "" Then
'Settings lost, at least make sure things can be accessed
For Each oBar In Application.CommandBars
oBar.Enabled = True
Next
Exit Sub
End If
Open sFilename For Input As lFile
On Error Resume Next
For Each oBar In Application.CommandBars
Input #lFile, sTemp, bEnabled, bVisible
If oBar.Name = sTemp Then
oBar.Enabled = bEnabled
oBar.Visible = bVisible
End If
Next
Close #lFile
Regards,
Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com