• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

toolbar and menus

Status
Not open for further replies.
lately i found that code

Code:
Sub HideExcelItems()
Application.DisplayFullScreen = True
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
End Sub

Sub UNHideExcelItems()
Application.DisplayFullScreen = False
Application.DisplayFormulaBar = True
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayGridlines = True
End Sub

for some reasons i want to make a use of that and change the interface of a workbook that i use a lot at office
so i am trying to make custom navigations panels to go to sheets and toolbars and menus to run macros
but always found problems
such as
i can use drop down lists because my sheets all have different columns width
combo box form control i find many codes to run macos based selection but i cant make them work right
combobox active xcontrols in my excel meets some problems(can't insert object)
i thought to make a userform from vba and put buttons but then i had to run macro to show the userform and then make the selection.
i searched for some addon but i use a version of excel that is not work properly with that

i am in deep searching nowdays but it isn't something that i need it actually so much
if anyone has any idea and want to help i will appreciate
 
Last edited by a moderator:
i found a working way for my purpose with that code

[Sub DropDown1_tameio()
Dim a As ControlFormat
Set a = Sheet2.Shapes("Drop Down 2").ControlFormat
Select Case a.Value
Case 2: Application.Goto Worksheets("sheet").Range("A1")
Case 3: macro1
End Select
End Sub]

i categorize my 22 sheets in 7 categories
and made 7 combo box to navigate based on category
with that code in main sheet is working.if i want to copy combo boxes to the rest of sheets i will have to write again the code and change "= Sheet2" and
("Drop Down 2") as the sheet needs and then to assign the macro to each combobox
so 22 sheets 7 combo boxes = 154 codes
a lot of work. 2 questions
"= Sheet2" can be change to active sheet or something?
if i copy combo box named "drop down 2" to another sheet
the copied combobox name changed to "drop down (new number)"
but from its properties can i rename it back to "drop down 2" ?
 

Attachments

  • TEST.xlsm
    22.5 KB · Views: 3
Last edited:
i am to a very good point with my toolbar.i have make a lot of progress
the only that bothers me and i can't find a solution is that
i have make comboboxes with form controls (not activex controls)
and i have assign cells that when i select a case from the dropdown to run a macro. for example in the first i have a title of combobox so the cases starts from 2. if i choose case 4 the macro that i have assign runs.
but i would like the combobox to return to first value that is the title of combo box as default. if my thread wasn't clear i can send a test or screenshot
 
Status
Not open for further replies.
Back
Top