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

The error is because the code below can't find the Options tab even though

No, the error is because you are trying to select a sheet in an add-in which
is hidden.

You should save options in the registry (SaveSetting/GetSetting) rather than
try to save the add-in, but for this code to work it should be something
like this:

Function Set_Options(MenuName As String, Show_Menu As Boolean) As Boolean
With ThisWorkbook.Worksheets("Options") 'Or better its Codename
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
End With
If CurrentlyVisible < Show_Menu Then
If Show_Menu Then
MakeTheMenu MenuName
Else
DeleteTheMenu MenuName
End If
End If
Set_Options = True
End Function

Btw, there is not much point to this being a function because it always
returns True and you're not using the returned value in any case.

--
Jim
"Tim879" wrote in message
...
|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