>>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" <(E-Mail Removed)> wrote in message
news:ec7df71f-eef6-45b0-aeee-(E-Mail Removed)...
|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
|
Similar Threads
