Sheet Navigation With Buttons
I'm going to be creating a workbook that contains a worksheet for each year. Within each worksheet, I will have a calendar format that contains a range for each month of the year (multiple columns and multiple rows). Is it possible to name areas based on month and then create a button or drop down list at the top that would allow users to select the month and the view would go to that area within the calendar?
View Complete Thread with Replies
Related Forum Messages:
Drop Down List For Sheet Navigation
Split off from
Dependent/Linked Drop Downs In Cells
Originally Posted by Dave Hawley
Try it now. You hadn't named the cells that the Validation lists are Dependant on. Not bad for a "moron", hey?
Things get pretty stressed around here, especially when your commander depends on you as much as mine, and just between the two of us, it is twice as hard when your commander is younger than you. Let me try it real quick. Auto Merged Post Until 24 Hrs Passes;Ok, that works great, now I have to go back and add all the real units and soldiers. Fun, fun, fun. BTW, you wouldn't happen to know how to add a first worksheet that would have buttons to take you to the different sections? I know how to do the hyperlinks, but I think I read something on here about comboboxes?? If I need to post this somewhere else, then just push me in the right direction. I am use to pushing in the Army, it's our life!!
Create Sheet/Worksheet Navigation Menu
Im building a Menu list for my customers. I want to do it in XL sheet format. Can i know how to create tabs (like hand should apprear,when cursor come to the tab and should be like button, where we can name it) in the main sheet?. I have lot of innovative Ideas to come with beautiful menu file i need your assistance further to build my menu list.
Create A User Friendly Sheet Index For Worksheet Navigation
Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.
Radio Buttons In Protected Sheet
I have a pair of radio buttons on an Excel 2007 sheet. I want the buttons to be clickable, but have the sheet protected.
Both are defined as unlocked. The Group containing the two buttons is also defined as unlocked.
But when I protect the sheet, one of the two buttons gets the pop-up saying the cell is protected - the other button is fine. I've even tried unlocking the cells behind the buttons, but it still pops up.
Scrolling Macro Buttons With Sheet
I have a spreadsheet containing a group of Macro Buttons, each button has been assigned a macro that enters specific text in the selected cell when the button pressed.
But as you scroll down the sheet, the group of buttons is left above. Is there a way to make the buttons scroll down (or up) along with the spreadsheet ?
Buttons Not Wanted On Copied Sheet
I am copying sheet 1 to the back of the workbook and renaming it. I am using a few command buttons on sheet 1 to trigger the copy macro, etc. I do not want these buttons on the copied sheets.
I have done a right click /Format Control/Properties and selected “Don’t move or size with cells” but the buttons are still being copied and still active for the macro. Perhaps due to sheet copy versus cell copy?
I guess I could add select and delete steps to the macro for the buttons but this seems excessive.
Protect Sheet With Macro Buttons
I have two macro buttons on a page that hide and unhide columns. When I got to protect the sheet .. the buttons no longer work and ask me to DEBUG. Is there a way to have macro buttons performing tasks AND have the sheet still protected?
In the protect sheet window .. am I have ticked is
Select Locked Cells
Select Unlocked Cells
But these are ticked as a default.
Copy/paste Buttons In New Sheet
I am using VBA to copy and paste the entire contents of a worksheet into a new sheet. However I have a button that I put on the sheet that needs to move aswell. I got it to move but the event handler for the button
Private Sub CommandButton1_Click()
that is stored on the first sheet and not copying with the button. Is it possible to copy code from one worksheet to another (or maybe store the event handler somewhere else?)
Enable/Disable Command Buttons From A Sheet?
How do I Enable/Disable Command Buttons from a Sheet?
I am using a Command Button in a Sheet to copy and paste the Data from one Sheet to another with the help of macro..
As I am not well-versed with VBA dont know all the syntaxes of VBA.
I need help for the command button..
First and Foremost, I double click a Command Button form the Control tool-box and paste it on the Sheet, I dont know how to get the name of this command button , I mean where do i get it?
Based on a condition like a value in a cell I want it to be Enabled and Disabled?
Any ideas...please I am not able to follow even after googling a lot as I dont know what's the name of the command button control I have used.
If the value entered in a particluar cell is more than the 1000 difference between two cells then the command button should be disbaled...
The Application part:
The command button is used to transfer the data in a cell lets say $I$4 to another sheet Cell J2,J3,J4 so on so forth..by incrementing the ROW number.
Now The balance gets depleted with every new Debit Entry and we need to disallow the user from entering such an amount which will reduce the balance more than The Minimum Account Balance of a bank...
Can We Know Which Button Was Pressed On The Sheet When The Buttons Are Created During Run Time
I have created a button name "Add Power Source" on excel sheet that adds a table and a button(named "Add Row") to the sheet.Whenever the user clicks the "add power source" button a table and a "Add new row " button is created .This "Add new row" button is used to add rows to the table.
Now suppose the user has pressed the "add power source" button 5 times,so that there will be five table on sheet and each having a "add new row " button associated with it.When the user wants to add a new row to the table 2 say .How will I come to know in which table the rows are to be added using VBA.
Graying Out Custom Menu Buttons When Sheet/book Protected?
is there a way to cause my custom menu items to grey out like the built-in ones, depending on the current selection or when the status of the active worksheet/workbook changes (e.g. when the book is protected to disallow certain actions)?
Obviously I can trap any problems once the associated VBA is invoked, but it'd be more professional if I could just prevent that from happening in the first place.
Hide Standard Buttons & Stop Work Sheet Flashing..
1: Is their anyway I can get rid of the File, Edit, View, etc buttons at the top of the document so everyone that opens it can not see them? and also the save button, the idea is they have to use the button to save the sheet.
2: I have a button on the sheet with a macro that saves the sheet once it has been worked on, the sheet flashes when this button is clicked is there any way I can stop the sheet flashing ?
Change The Color Of Buttons Or Command Buttons
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
I am trying to edit the Copy menus so that they only paste values. I have the following code in the workbook object:
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Application. CommandBars("Edit").Controls.Item("Paste").OnAction = "New_paste"
Application.CommandBars(" Cell").Controls.Item("Paste").OnAction = "New_paste"
Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
and this code in a module:.........
The module code is however bringing up an error message 'User-defined not defined' and I am not sure why.
Floating Navigation Window
I want to create a floating window in excel that can contain a series of macro buttons (12 total)
I'd like to see if this window can be opened with a hyperlink or a single macro button on a spread sheet.
Form: Textbox Navigation
I have a form set up with excel (2003) using vba. I'm doing a check to see if textbox1 is empty, if it is, a msgbox pops up with a warning that "textbox1 needs to be filled out". I click OK and the cursor goes to textbox2. I want the cursor to return to texbox1 without user intervention but can't figure out how. I'm using "Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)".
I know it can be done because I did it with vba Access, but I can't seem to find it using vba Excel.
Drop Down Navigation Query
What i want to do is create a drop down list in excel on a single sheet to navigate to a certain row of the worksheet.
I thought i coudl use data validation then use some code in a worksheet change event to skip to that section like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
Select Case Target.Value
Problem is it wasnt exactly what i was looking for as i cant use an array in data validation so what i thought was a good idea was a bad one.
what i would like is a drop down list that when i select a heading within it, it naviagtes me to that row where my heading is but also makes that row the row scroll up so it is at the top of the page....Hope this makes sense.
my row numbers are 4, 13, 19, 28, 37, 43, 52 62, 65 and 75.
Navigation Via Combo Boxes
I am trying to use the combo box feature to navigate between sheets. In other words, I have a dropdown menu at the top of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. Is this possible?
Navigation Of Dynamic Table
I have a large table where rows are added by copying either the top row or the bottom row and adding it to the bottom of the table. Below is the coding I was hoping to use to jump from cell to cell within the rows using a hotkey assigned to the macro.
Is it possible to apply the following code in a way that copies the same hotkey targets from row to row?
In other words, a row copied from row 1 to row 2 would still have functioning targets of A2, D2, L2 and Q2.
Select Case ActiveCell.Address
Case Is = [A1].Address: [D1].Select
Case Is = [D1].Address: [L1].Select
Case Is = [L1].Address: [Q1].Select
Case Else: [A1].Select
Navigation Of 2 Reports On One Worksheet
I have 2 reports on one worksheet. I have put a button at the top of each report to navigate back and forth. One report is located at a1:S27 - it shows up fine. The issue is the other report located at BA1:BT52. No matter where I assign a name to it the report doesn't come up full page with cell BA1 at the top left hand corner.
Custom Navigation Using Cursor Keys
Attached is a protected sheet. The yellow cells are unprotected. When the use is on the cell L3, and the right arrow is clicked I want it to jump to M6 and vice verser, in other words, navigate in order from left to right. Is it possiblwe to do this?
Easy Navigation Of Multiple Sheets
I have a workbook with over 100 worksheets (all worksheet tabs are named). Since the tabs are not sorted in a numerical order (they are grouped by departments hence the numerical tab order does not apply), can I search for a particular worksheet tab using a search function? The CTRL F function doesnt allow searching for worksheet tabs. At the moment, I have to go through the tab names in order to find one particular tab.
Navigation Through Spreadsheet Using Shortcuts - Not Range Names
I currently have a navigation menu with 9 options however these options are based on Range names that I have set up.
The concern is that if someone deletes the cell that contains the range name, obviously that option in the navigation won't work, nor would you want it to.
However if someone re-enters a cell that you would want linked up with the navigation there's a lot more action involved.
What i'm looking for is a macro that can be used through a button, Idealy i'd like the macro to search through column "B" and look for key labels (ex: "Ground Floor", "1st Floor" "2nd Floor" etc.) and have each button set up for it's respective search data (ex. Ground button searches only "Ground Floor")
Easy Navigation To Specific Ranges Or Areas
I have a spreadsheet that lists values for different grid lines. On top of each set of data, I have labels each line "Line A", etc. I want to put in possibly a pull down menu at the top of the sheet that the user could use to jump to whichever line they were interested in viewing. For example, if the user needs data from Line L, they could select Line L from the drop down menu and the spreadsheet would jump down to that area.
Navigation Keys Quit Working After Workshee Change Macro
why my keyboard navigation keys (Tab and arrow keys) quit working after the code below executes? Other keyboard keys like alpha or numeric characters and Enter work but I can't move to another cell without clicking it. Selecting another ws, then returning to the "Master" ws fixes the problem. I tried activating Master near the end of the code but that didn't help. Master is not a protected sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column 7 Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim NextRow As Long, MySheet As Worksheet
Set MySheet = Sheets(Target.Value)
NextRow = MySheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(Target.Row, 1), Cells(Target.Row, 6)).Copy .Cells(NextRow, 1)
With .Cells(NextRow, 7).......................................................
UserForm Navigation Bar Control Error "1004"
I am having some difficulty with user form navigation bar - when scrolling to the end of the data it works just fine. When scrolling towards the beginning of the data it breaks with a "Runtime 1004: Application-defined or object-defined error".
The reason may be that the beginning of the data is not correctly defined. The data has a header row. Code that I think is relevant to the navigation bar follows:
Private Sub Navigator_Change()
'When Scrollbar value changes, save current record and load
'record number corresponding to scroll bar value
Set RangeData = Range("CONTACT").Rows(Navigator.Value)
Private Sub UserForm_Initialize()
'Load 1st record in CUSTOMER and initialize scroll bar
Set RangeData = .Rows(2)
Navigator.Value = 2
Navigator.Max = .Rows.Count
Menu Bar/Navigation :: Tool That Creates A Menu Bar Or Code?
I have a workbook with multiple sheets. Is there a tool that creates a menu bar or code that I can add that would support navigation?
My goal is to have something with approximately 4 options (buttons?). They would link to 4 different sheets that serve as indexes within the workbook.
I know I can add a control like a button to a given sheet that would take the user to given sheet when clicked, but can I make the button 'float' above all the sheets, or add to a menu bar, or something? I just don't want to have to add the button to every sheet in the workbook.
I am looking for something simple/easy, and am not looking for anybody to do the work. I just have no idea on how to approach the problem.
Filter Using Buttons
i want to filter the sheets on click of a button .
like in this sheet i want to filter the column E in column E their are many reasons of non delivry of my products i want to find out which department is accountable for how much loss so on click of a button it filters with 2 reasons
1)Ko Cannister stock Out
2) Outlet Need fresh stock
Protect Buttons ...
Dear sirs,with best regards,i have in attached example file some cells i have protected them by ( tools-protect ) which is the general way for protection,for special purpose i ask you kindly to help me by:
creating a buttons with macros to protect and unprotect the ( cells,columns,rows,and all the sheet) that i need to protect them, when i want to (protect or unprotect) i have to click these buttons.
Using Buttons Over 2 Workbooks...
I currently have a sheet (SHEET-A) which pulls in a figure from a cell (CELL-G) in another workbook sheet (SHEET-B), and displays the value in CELL-X.
CELL-X then is used for a series of calculations on SHEET-A.
CELL-Y is adjacent to CELL-X and can have a number entered into it which is then used instead of CELL-X for the calculations on SHEET-A. (It is also incidently displayed over CELL-Gs value in CELL-X)
I would like to have a button next to CELL-Y which upon click alters the value of CELL-G in SHEET-B to that of CELL-Y in SHEETA.
I've got a small problem with radio buttons. I've been asked to add a few to a sheet we use at work, problem I'm having is when I add these new buttons from the forms toolbar and then assign the cell link for them some of the other radio buttons on the sheet asume the cell link of the new buttons and when I change them back the cell link for the new buttons change ? i could understand this problem if I had maybe copied them and modified the copies perhaps but that isn't the case.
I created a frame in a spreadsheet I put in 2 options buttons that I called Left the first one and Right the second one. I got 2 cells lets say C10 and G10 in these 2 cells I write the cell number values. When I click on button one or Left, I need the cell C10 be set focussed and write a number e.j 5. However, when click on the second butt to write a number e.j 7, the number in the cell C10 still there, so, what I need is a cero or an empty cell C10, and viceversa. Could you help to write a code for this problem.
Buttons Not Visible
I have created a great little macro to handle a large amount of data on my spreadsheet that I've been using for several months. It's a shared sheet that is used by many people, but the macro is protected...That being said, something strange has happened that none of my buttons for activating the macro are no longer visible. I can't even create new ones. I get a box outline with a little red X in the upper LH corner. I've verified that the button is in fact set to be visible and set up properly for colors, etc.
I'm not sure what's happened since it happened in a matter of hours...did something get "deactivated" that I need to re-instate?
Multiple Toggle Buttons
I am having a small issue with my toggle buttons.
I have approximately 20 sheets that all have 4 toggle buttons on them. Coded as below (with different ranges for each button). So when I wrote them I took the easy (for me anyway) way and copied all the code for all the buttons in each sheet. (Buttons are named Zoom1, Zoom2 etc.)
Private Sub Zoom1_Click()
If Zoom1.Value = True Then
ActiveWindow.Zoom = 143
ActiveWindow.LargeScroll ToRight:=-10, Down:=-10
ActiveWindow.Zoom = 75
ActiveWindow.LargeScroll ToRight:=-10, Down:=-10
Round Option Buttons
the code below produces a message box with square command buttons. Anyone know how to make it appear with round option buttons?
ans = MsgBox("Are you sure you want to clear the entire workbook?", vbYesNo, "CLEAR ALL")
If ans = vbNo Then
Application.CutCopyMode = False
If ans = vbYes Then
Creating Option Buttons
I want to have 2 groups of option buttons, and create them similar to the way I've created textboxes (as shown here):
With Sheets("sheet1").TextBoxes.Add(10, 5, 100, 100)
.Interior.ColorIndex = 4
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Caption = "Check Selection"
.Border.Weight = xlThick
When I try:
With Sheets("sheet1").OptionButton.Add(20 , 5, 100, 100)
I receive a runtime error 438
Also, how can I set up the grouping options, and have a default selection already picked?
Resetting Option Buttons
I have a spreadsheet to keep track of purchase orders, and I use groups of option buttons to run macros to filter the info by category using auto filters. Not sure if I'm explaining this well, but as an example, I have one set of option buttons for whether the items are late, and one for whether I have flagged the order for some reason, and a couple of others. It does everything I want, basically.
I have a button which resets all the autofilters in one go, but the problem is it doesn't put the option buttons back to the "Show all" option for their categories. I tried recording a macro and clicking in the "Show all" option so I could look in the code to see how it refers to it, but it just says to run the macro, nothing about selecting "Show all".
Also, the way I reset all the autofilters is to have a macro that goes to every autofilter in turn and selects "(All)". Is there a better way of doing this?
UserfForm That Contains A Few Buttons, Textfields Etc
I have a userfForm that contains a few buttons, textfields etc. And at the workbook, I have a few sheets that shows different data.
1) Is it a must that the userForm need to be in the excel workbook? Can we export the UserForm out?
2) What can I do to make sure that when I click at one of the button at the userForm, a new window would be popped out and show only a specific worksheet in the workbook?
3) Is loop the only method to check a list of record against another list of record? I find that when I used looping the processing time is long.
4) what is the method that I can use so that the cursor will always stay at a particular textfield?
Workbook With Some Command Buttons
I have a workbook with some command buttons that I created a while back. The buttons used to work fine. But after installing a new computer, the spreadsheet no longer works. I can step through the code fine, but when I try to click on the button, nothing happens.
Option Buttons And Images
I have in a Useform 4 OptionButtons. This is working Ok to a point. My Question is, I have 12 images. I like to show a particular image depending on what option is choosen. As of right now if Optionbutton1 is selected. The right image is shown. But if 2 OptionButtons are selected the wrong image can be shown. So, it leads me to think I need and if or statment in the code? If so how can this be done in a example.
Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then Sheets("HAAS INT. NPT THD. 1 PASS").Range("E10") = "M03" '= CW RH HAND THREAD
If OptionButton1.Value = True Then Me.Image2.Visible = True 'Conventional Int. Left Hand
If OptionButton1.Value = True Then Me.Image3.Visible = False 'Climb Int. Left Hand
If OptionButton1.Value = True Then Me.Image4.Visible = False 'Climb Int. Right Hand
If OptionButton1.Value = True Then Me.Image5.Visible = False 'Conventional Int. Right Hand
Private Sub OptionButton2_Click()
Buttons Should Float In The Worksheet
I have buttons (macros) on top of my worksheets. The sheet might contain more than 1000 rows.
Now, what I want is that the buttons should remain constant on top (floating) horizontally and vertically even when the sheet is scrolled down or right etc.
An example would be Windows | Freeze Panes. But I need an alternative.
Command Buttons Not Working
i try run macro with commandbutton, but it doesnt work. I insert button from "control toolbox" then i create a macro. Then i wrote this macro to "Private Sub" but it still doesnt work.
When i click on button, it only open editing macro, or select the button.
The Philosophy Of Option Buttons
I noticed that it has a frame, and within the frame are three option buttons. I became quite curious as to the relationship between option buttons and the frame. With no coding on my part, the little buttons seem to mystically sense their presence inside of a frame, and dutifully acquiesce to whichever new option is clicked, so only one option at a time may be true. I decided to experiment. I created one lone option button outside of the sacred frame. I was able to click it on, and, lo and behold, I could not click it off! When I cut and pasted it inside the frame, with its brethern, it automatically became obedient. Well, I pasted it again outside the frame, and experimented with how I might turn it off. Now, my perverse nature began to sense that such behavior is the venue of a checkbox and not an optionbox. But, like Don Quixote, jousting at windmills, I was determined to have my way. The only methods I found to turn a lone ranger option box off, once checked, was to use the keydown option, and set it to false, which is obscure and tacky to say the least, or to place a label next to it, entitled, "turn the blasted thing off" and, in its click event, set the option button to false.
What I deduce from all of this is that the proper function of the option button lies within the boudaries of a frame, with one or more brethen option buttions. Am I correct in my surmize? And otherwise, one should be using a check box, which may be toggled on or off to the heart's content?
Hmmm. I have not thought to test the function of checkboxes residing within a frame (until just now!).
Experimentation reveals that if I add several check boxes to the frame containing the option buttons, there is no apparent affect. The option buttions still obey the frame rules for radio button logic, and the check boxes may be toggled on and off at will.
So, I ask myself the next obvious philisophical question, namely "what would be the use for a lone option button that, once turned on, could NEVER be turned off.
Well, in our society, I can think of two obvious applications.
One: "Have you ever been a member of the Communist Party"
Two: "Have you ever been attracted to the same sex."
Now, in our society, once there is the slightest hint of either option, it can NEVER be turned off. Even if you need better reading glasses, and you mistook "same sex" for "SOME sex", no matter, you checked the option and now you are branded.
And if you want to cancel my form, think again, because my VBA code updates with ever change event.
This option button philosophy suggests to me an interesting social commentary. I was once asked to write a literary analysis of the movie, "Brokeback Mountain" and the short storie by Annie Proulx. I asked various people, randomly, if they had seen the movie. I asked one African American male, and he recoiled from me and say "Ewww, you would watch that movie." In his mind, I had checked that option box, and there was no turning back. I gave him the following explanation. "Imagine an athlete who was notorious for seducing over 1000 women, but suddenly the tabloids revealed that as a teenager, he had one same-sex involvement. What would you say of him?" Of course the answer was that he was one of those "brokeback mountain" folks. So, then I asked him, "well, suppose someone was notorious for 1000 same sex involvements, but the tabloids revealed that, as a teenager, he had a relationship, with a girl?" Well, we know the reaction, same thing "a brokeback mountain fellow."
So, I progressed to a similar analogy. I said, "You see me as Caucasian, but, suppose you learned that my great great great great grandparent was African? Would you then see me as African-American? But, if your great great great great grandparent had been Caucasian, would you consider yourself Caucasian." He became quite flustered and said "that is different."
But, aha, is it really so different? Therefore, in our society, in our values, in our minds, in our stereotyping of others, we do require the data type of the lone optionbox, which can only be checked ON, and once on, can NEVER be checked off.
And, yes, this post is pertinent to excel vba forms, because the purpose of our forms and our datastructures is to accurately mirror our thinking, our values, and our society.