Switching Between Sheets - Shortcut
I often find myself moving from one sheet in a workbook to another over and over. I wanted a shortcut that moved between the last two sheets selected.
Similar to how Alt+Tab works with windows.
Does anybody know a keyboard shortcut or if not is there a macro that could be added to perosnal.xls that would mean i would aways b able to switch between two sheets quickly.
View Complete Thread with Replies
Related Forum Messages:
SWITCHING BETWEEN FORMULAS
I am trying to figure out commissions with 3 different commission structures depending on type of product. So on C2 I have a drop down box with the 3 types of products. So what I would like to do is when C2 shows say "New" I want it to calculate using one formula. When "used" is selected, I want to use a different formula. And when "scratch_dent" is used, I want it to use a 3rd formula.
Working for a dutch company and using a lot of excel and access it is sometimes a pain that when you are looking for functions. for example "left(b3;3)" in dutch is "links(b3;3)" now this is an easy one but some are hard to guess.
I wrote some neat code which condionally formats cell's in an Excel range and dependend on a value in column "Q" (= status) then sets the color of the field. it works fine
However when working with an english version of Excel I need to use "left" instead of "links"
how to check the local settings for Excel?
'Conditional Format status cell's
.Range("G4:P" & (xRcount + 3)).Interior.ColorIndex = 4
iRow = 4
iRow2 = 4
US/UK Date Switching
I want the people filling in the form to put the date into a box. I did have a calendar box but this wouldn't work on a lot of PCs , so I've switched it for three combo boxes, Day-Month-Year.
I've got the 'Add' button to put it all together - DayBox.Value & "/" & MonthBox.Value etc
This seemed to work fine and excel processes this as a date. It automatically switches to the US date format (MM/DD/YYYY) which would be fine as users don't see it and I know it's at least consistent.
But it's not.
1st August 2009 = 08/01/2009 (ie MM/DD/YYYY)
13th August 2009 = 13/08/2009 (ie DD/MM/YYYY)
And while I can format cell to show me the month name in the first example, the 13/08/2009 refuses to change format even though it's recognised as a date.
Switching Between 2 Workbooks
From a workbook, I need to open a further workbook called 'transfer' and then switch back to the original workbook. Presumably I need to save the current workbook name to a variable, open transfer with Workbooks.Open Filename:="c:debworkTRANSFER.xls"
then switch back to the original
How do i pass the original workbook name to a variable, and then reactivate it. I have tried numerous options but just cannot get it to work. Do i reactivate with workbook. activate or windows.activate
Switching Between Workbooks
I'm trying to write a piece of VBA code which will switch back and forth between 2 workbooks, but I was hoping that I would not have to name both workbooks in my code.
So far I have been able to name the second file as a variable, open it and copy the data I need, but I now need to switch back to the original file. Is there any way of doing this without hard coding the file name?
Prevent Tab Switching
I made up a small subprocedure that searches through a large list of zipcodes (These zipcodes are in a separate sheet). Whenever I execute my subprocedure in another sheet, it jumps to the zipcode sheet and then goes back to where I originally executed the subprocedure.
Is there any way that I can prevent the sheets from switching back and forth?
Switching Between Multiple Spreadsheets
I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.
Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.
Switching Between Opened Workbooks
I have 3 workbooks open, the main WB has my command button + code in it, and the other 2 were opened up using the existing code in my main workbook.
Using general code e.g( Thisworkbook, activeworkbook, workbooks(1)), how can I activate one out of the 3?
I know the main workbook can be accessed using "thisworkbook", but when I activate it, I can't activate one of the others in my next line of code using activeworkbook.activate, or workbooks(1).activate.
Switching Languages: Formulas Vs. VBA
I am currently creating a massive bilingual report using Excel based on data gathered from a customer visit, as most of the engineers are non-native English speakers, and most everywhere else in the world wants reports in English. Currently I am using this roundabout method:
I have a lookup Table sheet: .....
Switching Between Workbooks Without Having To Use The Activate Command
Is there a way to switch back and forth between workbooks without having to use the Activate command? I have everything planned out but this aspect is still a source of confusion. The Project: I need to have qty purchased and price in a table from Worksheet B entered into the matching customer index location in Worksheet A. Worksheet B contains customer number, sku, qty purchased and price. Worksheet A contains customer numbers in row 3 and sku's in column A. Minor data manipulation will need to be done on Worksheet B before before accessing it which I plan to do after opening it in this macro.
Selecting A Range When Switching Between Workbooks
I select workbook 1, cell A4, then I select workbook 2, cell A4, but when I'm in workbook 2, it does not select cell A4. Am I doing something wrong here? See my code below.
Dim i As Variant
Workbooks("1_2007 Forecast (Ancillary).xls").Worksheets(1).Activate
Workbooks("1_2007 Forecast (LGBU).xls").Worksheets(1).Activate
Macro For Switching Autofilter Between Values
I have a column in a sheet that has two different values in it "SB" and "SEI".
I want a macro that will allow me to switch the auto-filter between the 2 at with a shortcut button.
I have a macro that will switch the auto-filter on and off, but I was wondering if anyone had anything that will automatically switch it between two values.
Delay In Switching Between Workbook Windows
Workbook Name Changes – But Workbook Does Not For Five Seconds?? MS Office Excel 2003 / SP3 Installed. I have noticed an odd occurrence from time to time while switching between workbooks. When I switch from one workbook to another, (typically via the “Window” pull down – but the same result happens when switching workbooks on the tabs at the bottom of the screen), the “switched to” workbook name will instantly appear at the top of the application, but the actual switch to the selected workbook is delayed for several seconds, (five seconds or so).
While not a major issue, it is annoying. I suspect that it is a memory allocation issue, (I have a Duo CPU System / XP Pro / 2 GB RAM), and with that in mind I have both restated MS Excel and also performed a complete re-boot. While either option does (seem) to result in the issue being (temporally) removed from my machine, it does return. I may go for several days without this issue occurring, and then it appears for no known reason
Switching Two Workbooks And Transferring Data With Variables
I have create a nice invoice with macros to random insert pages and summations etc. Works fine, but to complete it I want to go one step further and there ìt got out of hand.
I want to create the following, but I can not get al this combined in two Macros.
Basis: Workbook 1 = Invoice, Workbook 2 = Invoicelist
Required actions Macro1:
1. Starting point is that Workbook “Invoice” is open.
2. The user activates Macro1.
3. Check if Workbook “Invoicelist” is active.
4A. If YES then continue,
4B. If NOT then reply with some sort of Alertbox “Invoicelist not active! Click OK to open (needs to open Workbook “Invoicelist” or Cancel to stop (needs to stop Macro).
5. Workbook 2 “Invoicelist” is now open.
6. Find the last invoicenumber in the list. Add “1” and put the new number to the list (to prevent using double numbers). Then I want to take this number to the “Invoice’ workbook, but that can be done by copy and paste.
Now I get really stuck because I want to use variables instead of multiple copy paste actions.
Required actions Macro2:
7. We are in Workbook “Invoice”, the user activates Macro2.
8. Same check if Workbook “Invoicelist” is active as in Macro1.
Several data from “Invoice” has te be transfered to “Invoicelist”. And to be safe, this does not have to be the last cell if the user “forgot” to store date for a previous invoice!
9. Store data from sevaral cells in variables, including invoicenumber!
10. Find invoicenumber in “Invoicelist”.
11. Paste data from variables into fields on same row as correct invoicenumber.
12. Save Workbook “Invoicelist”.
13. Go back to Workbook “Invoice”.
The two problems I cannot fix is checking if Workbook is Active and store and use cell values with variables.
Switching Between Workbooks Whose Names Are Stored As Variables
I'm having trouble finding a way to switch between two workbooks that I have open whose names are stored as variables. If the variable name were variable, for example, I have tried the following:
none of these work and I've tried a ton of other ways to get it to work but I just can't figure it out!
Copy And Paste Macro W/o Switching Worksheets
I'm writing some macros in excel in an effort to transfer over from Lotus 1-2-3 but have hit a major roadbump. The macro is fairly complex and takes a while to run and I'm trying to cut down on the Run Time. As of right now lotus does it about 6x faster. I think a big roadblock here is that in the macro I built in excel when copying and pasting values into another worksheet, the coding has to
1)select the cell to copy
3)select the cell to copy to
4)paste or paste special into that cell
5)switch back to first worksheet
for every single value I want to copy over. With lotus it was possible to just copy values to an defined name in another worksheet without leaving the current one. This would greatly decrease runtimes and I was wondering if anyone knew how to code for this.
Control Losing Focus Switching Applications
I'm using a VBA UserForm (ShowModal=False) as the front-end on a spreadsheet for logging purposes. Here's the progression of the problem:
1. I have the UserForm loaded and the cursor is in any given textbox/ combobox on the form.
2. I switch to another program, then come back to the UserForm.
3. The cursor no longer appears in whatever textbox/combobox I was in when I left the UserForm. If I type, nothing happens (I have to click the field again first, then type).
However, if I TAB, it will go to the next field in the Tab Order just fine. It seems that the control has "pseudo-focus"--it knows which field to tab to next, but the control won't accept input unless you click it. The odd thing is--this UserForm has a button which launches another "child" UserForm. That "child" UserForm does not have this problem.
Using Ctrl+M As Shortcut
i have tried using the letter M as a shortcut key (because it is to activate a "Move" function) but unfortunately it doesn't work. I gather from this that not all of the keys are available as shortcut keys presumably because they already have function.
Is there a way around this? and/or is there a list somewhere of the available shortcut keys?
i need to create a shortcut (ideally ctrl-z) that will allow me to search column D which has a tonne of street names in it. i need to do a "custom" search and enter "contains" and then have the field on the right be left empty for me to enter what i need to enter.
I want to hit ctrl-z (or a b c d e f g h etc...) and have that blank field brought up for my to enter something to search for and have all those criteria met "custom-contain-?whatitype?"
I've tried using the record new macro function but it won't let me leave the dialogue box open for me to enter my street name.
i hope i explained that well enough....
basically i want to hit ctrl-z and have that custom-contains for column d open and ready for me to search.
autofilter is on. and i am referring to the drop down menu that lists "top 10" "all" and "custom" i want to chose custom and then chose "contains" from the first drop down menu in the dialog box that pops up.
Shortcut To Copy As Image
I'm copying a lot of tables and charts etc in Excel to paste into PowerPoint. And I'm using the mouse to go into Past > As image > Copy as image
I then get asked a question that I need to hit the enter button to answer.
I'd like to make a shortcut for this. Is this possible in VBA?
Using Excel 2007 & Vista if that matters
Font Color Shortcut
I am just looking for a keyboard shortcut macro. I change the font color in rows one at a time to either blue or red by selecting that row and using the format toolbar to select the color I want. Is there a macro shortcut for changing font color?
Even further, can the macro be able to change the font color of the entire row without highlighting the entire row? Meaning if I only have 1 cell selected within the row I want to color the font and apply the shortcut, can it color the font in the entire row???
Shortcut - Select All Worksheets
I was checknig here for shortcuts...
I wanted to find a shortcut to select all sheets in a workbook. There are about 30 worksheets in my workbook, and selecting all is a pain.
in Excel 2003
Launch A Desktop Shortcut
I have a lot of shortcuts to movies for my 5th grade students which won't work. I can launch direct file names but not shortcuts from within excel I can click on a cell to launch a direct file like this = "C:Program FilesOfficexpOffice10winword.exe" /n "c:mode.doc" but if C:modeSC.doc" is a desktop shortcut then it opens winword but not the document.
I get " document name or path is not valid" from within winword. But if I go to drive C and click on the shortcut, it opens winword and the ModeSC.doc which is a 1 KB shortcut.
Launch Shortcut Through VBA
Is it possible to launch a program through desktop shortcut with VBA? I have the following code and it is giving me an 'Invalid procedure call or argument' error
Edit: Typing the string into the Run command works fine.
Dim objWsh As Object, _
strDesktop As String
Set objWsh = CreateObject("WScript.Shell")
strDesktop = objWsh.SpecialFolders("Desktop") & "Wildfire 3.0.lnk"
Set objWsh = Nothing
Shell strDesktop, vbMaximizedFocus
Disable All Shortcut Keys
I want to disable ALL shortcut key functions in my program. (i.e. if a user presses Ctrl+S it will NOT save, Ctrl+O will NOT open file, etc).
Of course I need this enabled again in something like workbook close for users to have this ability in other workbooks.
I'm trying to make a macro that will help with data-entry. In two columns of each sheet (columns D and J), I'm entering school grade information ("K", "Pre-K", "1", etc, up to "12"). As I move off of each cell with data so-entered, I want to have the entry formated such that "1", becomes "1st", "12" becomes "12th", etc. I've put code into "Worksheet_SelectionChange" and the code works but...
1. The cell value is not changed upon leaving the cell, only after re-entering the cell. So, I type "1" in the cell, move on (the cell value remaining as "1"), then go back to that cell and the value becomes "1st" like I want. So each cell requires entry and then (for example), left arrow, then right arrow, then move on the next cell for data-entry. Since I'm only saving a few keystrokes for each cell, this approach is just about a wash. I need to have the data changed upon leaving the cell. Is there a way?
2. There are 50 or 60 sheets in the workBook and I have to copy that code onto each sheet. Is there a way to move it to the workBook level? Failing that, is there a way to ease the process of pasting that code to each of the sheets?
Assign Password As Shortcut
have a pain constantly entering passwords to open workbooks, is it possible to assign passwords to a particular short cut so if for example if I hit CTRL J it will automatically enter the password jabgat2009 and open the workbook- no big deal just if someone know of the top of their head
Shortcut Key Listing
Need way to list the active shortcut keys? I know there are manyy lists of the desfault shortcuts, buit I'm trying to generate a list of the curreently active shortcut keys & what macros they tie to. I have a vague recollection that someone had a VBA means of doing this, but I cannot find it in the forums I've searched.
Assigning Shortcut To A Macro
when we record a macro it gives us a option to assign a shortcut key to that macro how ever that is only in combination of "Ctrl" my question is that is there a way by which we can use any other combination like "Ctrl"+"Shift"+ <key>
Assigning Macro Shortcut
I have a workbook that when I open it opens a toolbar that was designed for it. I have been asked to make changes to this toolbar. One of the changes is to allow shortcut keys to run these modules. I have tried recording a macro and viewing the code, I tried applying this code there but it does not using the shortcut key. note that this toolbar is only available to this workbook, so these macros are not available to the personal workbook.
Shortcut To Formula Box
The one thing that has always bugged me about Excel is that in order to modify an existing cell, I need to click on the cell and then click in the formula box above. If I want to overwrite the contents of the cell, I can just start typing, but if I want to edit it, I need to use the mouse. It really seems like like Alt-D should move up to this window like it does in Internet Explorer. I can move around Most Windows applications without need of a mouse save for this one area.
Referring To Range() Using Shortcut Notation
I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.
For example, Range("A1:B10") would be [A1:B10].
I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?
If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.
Add Shortcut Key To A Button
When i have an excel sheet opened and hit ctrl +f the find replace window displays. I want to add a button on spreadsheet that when i click on it, the find and replace window appears, I can not figure out how to do this. I tried using .onkey
Shortcut To Copy To Main File
I receive everyday 100 excel sheets contaning data out of which i have to copy only 4 cells- amount, name, invoice number, vendor name into the master excel sheet. So i every time one all the excel sheet one by one and copy particular cells- e3, h45, l9,x0 and paste them one by one in the master file which i had mainted to treack the invoices issued.
Is there any shortcut where as i can minimize my copy paste work and click a button after opening each file- which i think i have to do as there can be no shortcut to it, and the 4 cells can be copied to my master file.
Shortcut For Navigating Between 2 Worksheets In The Same Workbook
is there a shortcut for navigating between 2 worksheets in the same workbook...
for eg if i am copying some values from sheet 1 into sheet 2 of the same workbook...wat i am doing now is clicking sheet1 copying the value then clicking sheet2 and pasting the value in the cell i want.. i want some shortcut ( if its there ) like ALT+TAB which is for navigating between different open windows.
Run The Shortcut To Stop The Add-in
I have an Excel add-in that makes use of an Application-level event handler to detect when workbooks are activated / deactivated, and adjusts the command bars / ribbon accordingly. The add-in also includes a developer mode, so I have a shortcut key to stop the add-in while running, set the .IsAddIn property =False, and then change whatever settings I feel that I need to.
Problem is, when I run the shortcut to stop the add-in, and then run my shortcut to restart it, my event handling no longer works. I've done some MsgBox testing, and verified that the class module's "Initialize" event gets called, but subsequent events (i.e. WorkbookActivate) go unnoticed.
I'm wondering if Excel will allow me to start an Application event handler using WithEvents, stop it (by setting the class module variable = Nothing), and then re-start the event handling.