Also if u want to add worksheet navigation button on right click try this
*Add below code to workbook open module*
Private Sub Workbook_Open()
On Error Resume Next
'Delete the new button if already exists
' name of the new button is "New Button"
Application.CommandBars("Cell").Controls("Worksheet Navigation").Delete
'run a macro to add a new button on mouse right click
Call add_new_button
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
' delete the btton when workbook is closed.
' name of the new button is "New Button"
Application.CommandBars("Cell").Controls("Worksheet Navigation").Delete
End Sub
*Add below code to module1 or any new module*
*
*
Option Explicit
Sub add_new_button()
' macro to add new button with name "New Button"
Dim cBut As CommandBarControl
On Error Resume Next
' name of the new button "New Button"
Application.CommandBars("Cell").Controls("Worksheet Navigation").Delete
Set cBut =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup,
Temporary:=True)
' name of the new button is "New Button"
cBut.Caption = "Worksheet Navigation"
' name of macro which you want to run when u will click on it
cBut.OnAction = "new_button_macro"
End Sub
Sub new_button_macro()
Dim cbut2 As CommandBarControl
Dim cmda As CommandBarControl
Dim i As Integer
Dim wk As Worksheet
' delete all exisitng buttons if any added on "New Button" Further"
For Each cmda In Application.CommandBars("Cell").Controls("Worksheet
Navigation").Controls
On Error Resume Next
cmda.Delete
Next
' run a loop and add new buttons further on "New Button"
For Each wk In ActiveWorkbook.Sheets
Set cbut2 = Application.CommandBars("Cell").Controls("Worksheet
Navigation").Controls.Add(Type:=msoControlButton)
With cbut2
'button name
.Caption = wk.Name
' macro to be assigned on button
.OnAction = "activate_sheet"
If wk.Visible = True Then
.FaceId = 351
Else
.FaceId = 352
End If
End With
Next
End Sub
Sub activate_sheet()
Dim ans As String
' check if sheet is hidden
If
ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Visible
<> xlSheetVisible Then
ans = MsgBox("This Worksheet is currently hidden. Do you want to
unhide ? ", vbOKCancel, "Please Answer")
If ans = vbOK Then
ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Visible
= True
ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Select
Else
Exit Sub
End If
Else
ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Activate
End If
End Sub
On Wed, Oct 2, 2013 at 11:42 AM, ashish koul <koul.ash...@gmail.com> wrote:
> try this
> Sub test()
> Application.CommandBars("Workbook tabs").ShowPopup
> End Sub
>
>
>
>
> On Wed, Oct 2, 2013 at 11:26 AM, Jewel Sharma <jewelsha...@gmail.com>wrote:
>
>> Hi,
>> I have a workbook with 8 worksheets; wherein I anticipate the user would
>> move to and fro between them in the course of using the workbook. To
>> simplify navigation, I intend to do the following:
>>
>> 1. Create a Userform that has labels for all worksheets.
>> 2. Such userform should be always visible in the right hand corner of
>> the application window.
>> 3. Use the Label_Click() event or CommandButton_Click() event to let
>> the user select the worksheet they need to work on.
>>
>> What code should go in the
>> Private Sub Label1_Click()
>>
>> event to SELECT/ACTIVATE "Sheet1". Please advise.
>>
>>
>>
>> I need such a Userform based worksheet navigation approach as I need to
>> hide sheet tabs, scroll bars, row/column headers.
>>
>> Jewel
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
>
>
> *Visit*
> *My Excel Blog <http://www.excelvbamacros.com/>*
> Like Us on
> Facebook<http://www.facebook.com/pages/Excel-VBA-Codes-Macros/151803898222297>
> Join Us on Facebook <http://www.facebook.com/groups/163491717053198/>
>
>
> P Before printing, think about the environment.
>
>
>
--
*Regards*
* *
*Ashish Koul*
*Visit*
*My Excel Blog <http://www.excelvbamacros.com/>*
Like Us on
Facebook<http://www.facebook.com/pages/Excel-VBA-Codes-Macros/151803898222297>
Join Us on Facebook <http://www.facebook.com/groups/163491717053198/>
P Before printing, think about the environment.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
