T
Tim879
I have an add-in that I'm working on which runs various macros from
menus, toolbars or the right click menu.
I would like to give the users the option of which menu (s) to create
but I can't figure out how to save their settings so that when excel
is opening the next time, the users settings are also loaded.
I tried creating a tab called "Options" in the add-in but once I set
the IsAddin property to true I get run-time errors when I try to save
the user's settings. The error is because the code below can't find
the Options tab even though it exists (and is spelled correctly) in
the addin.
Here's the code that is excuted when the user clicks ok on the Options
form:
Private Sub CommandButton1_Click()
If cbMenu Or cbToolbar Or cbRightClick Then
'next create the menus the user wants
If cbMenu = True Then
x = Set_Options("Menu", True)
Else
x = Set_Options("Menu", False)
End If
If cbToolbar = True Then
x = Set_Options("Toolbar", True)
Else
x = Set_Options("Toolbar", False)
End If
If cbRightClick = True Then
x = Set_Options("RightClick", True)
Else
x = Set_Options("RightClick", False)
End If
Me.Hide
Else
MsgBox "At least 1 item must be selected, please make your
selection and try again"
End If
End Sub
Function Set_Options(MenuName As String, Show_Menu As Boolean) As
Boolean
Sheets("Options").Select
Select Case MenuName
Case "Menu"
CurrentlyVisible = Range("A1").Value
Range("A1").Value = Show_Menu
Case "Toolbar"
CurrentlyVisible = Range("A2").Value
Range("A2").Value = Show_Menu
Case "Rightclick"
CurrentlyVisible = Range("A3").Value
Range("A3").Value = Show_Menu
End Select
If CurrentlyVisible <> Show_Menu Then
If Show_Menu Then
MakeTheMenu (MenuName)
Else
DeleteTheMenu (MenuName)
End If
End If
Set_Options = True
End Function
menus, toolbars or the right click menu.
I would like to give the users the option of which menu (s) to create
but I can't figure out how to save their settings so that when excel
is opening the next time, the users settings are also loaded.
I tried creating a tab called "Options" in the add-in but once I set
the IsAddin property to true I get run-time errors when I try to save
the user's settings. The error is because the code below can't find
the Options tab even though it exists (and is spelled correctly) in
the addin.
Here's the code that is excuted when the user clicks ok on the Options
form:
Private Sub CommandButton1_Click()
If cbMenu Or cbToolbar Or cbRightClick Then
'next create the menus the user wants
If cbMenu = True Then
x = Set_Options("Menu", True)
Else
x = Set_Options("Menu", False)
End If
If cbToolbar = True Then
x = Set_Options("Toolbar", True)
Else
x = Set_Options("Toolbar", False)
End If
If cbRightClick = True Then
x = Set_Options("RightClick", True)
Else
x = Set_Options("RightClick", False)
End If
Me.Hide
Else
MsgBox "At least 1 item must be selected, please make your
selection and try again"
End If
End Sub
Function Set_Options(MenuName As String, Show_Menu As Boolean) As
Boolean
Sheets("Options").Select
Select Case MenuName
Case "Menu"
CurrentlyVisible = Range("A1").Value
Range("A1").Value = Show_Menu
Case "Toolbar"
CurrentlyVisible = Range("A2").Value
Range("A2").Value = Show_Menu
Case "Rightclick"
CurrentlyVisible = Range("A3").Value
Range("A3").Value = Show_Menu
End Select
If CurrentlyVisible <> Show_Menu Then
If Show_Menu Then
MakeTheMenu (MenuName)
Else
DeleteTheMenu (MenuName)
End If
End If
Set_Options = True
End Function