Keyboard Shortcut For Switching Through Worksheets
I need keyboard shortcut for switching through worksheets.
For now, I have this VBA macro and shortcut Ctrl+R:
View Complete Thread with Replies
Related Forum Messages:
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.
Keyboard Shortcut: Copy Formula
Is there someone who know the keybord shortscuts for copy a cell formula, whithout using mouse. Like this: A1: =VLOOKUP(I2;A:A;1;FALSE)
Then copy the cell formula to the end of the sheet:
With the keybord.
Keyboard Shortcut To Highlight All Cells In A Column From Bottom To Top
What is the best way to highlight all cells in a column from bottom to top?
For example, if I'm in column O, row 138, what keyboard shortcut would I need to select ONLY and ALL cells from the row I'm currently on, (in this example, 138) to row 1 and ONLY in that particular column?
Keep in mind that the next time I may be starting at row 1,200, it could be different everytime.
I tried everything I could think of..
and nothing worked to select everything in that column from the row I'm currently on, to row 1.
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.
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
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.
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 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?
VBA (Keyboard Strokes)
I've programmed in many different forms of BASIC in my days, but I'm having too much trouble with VBA, and can't figure out if it's me or if it's the version of Excel I'm using (2002). I wrote a program in Excel, where if certain cells contain values other cell's calculations will become active. I want to click on a button that I created on the spreadsheet, then be able to select different keyboard strokes to activate these cells. I understand that there are 3 event codes that circle around this concept and that is KeyDown, KeyUp and KeyPress. Whenever I type in what MS Help told me to type in for these events, it always returns a runtime error.
Does anyone know if there is anyway that we can command Excel to avoid accepting aNY KEYBOARD ENTRIES? what intend to do is restrict a certain region on a worksheet for Users not to be able to type anything but but there are commandbuttons on the header which use: Range("C65536").End(xlUp)(2, 1) = "Value for the last cell" code to get the input into the cell. The purpose is to make use of the cmdbuttons with mouse but the data notto be keyed in !!!
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 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.
Flushing The Keyboard Buffer
I've run into a problem and I cannot resolve. I am designing an Excel Application in VBA. During the execution of the VBA code, the user will press different keys on the keyboard. These keystrokes are determined by using the GetAsyncKeyState Function. The problem happens when I exit from the VBA code and move back to a worksheet outside the code execution.
Lets say a user has pressed the 'Q' and 'X' key just prior to the final VBA subroutine running and releasing the worksheets back to the user. On the worksheet, in the selected cell will appear the 'Q' and 'X'. For some reason these keystrokes are not 'flushed' from the keyboard buffer prior to the code completion and are being transfered to the active worksheet. This is really annoying if I am testing the code in the VBA editor, because the spurious keystrokes will be placed in my VBA code, causing me to search for the out of place characters.
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
Keyboard Shortcuts For Existing Macro ?
Some expert users can unhide a sheet in a worksheet which is protected by password. But I don't want to place a button in the worksheet for everyone to see and wonder what it's for. I can record a macro and assign a keyboard shortcut but I can't do it it with existing macros ?
Open Hyperlink With Keyboard Keys
I use keyboard commands in my workbook, but I cannot seem to find one that will open a selected hyperlink.
The hyperlinks have a formula like this:
=IF(B1018<>"",HYPERLINK(CONCATENATE("I:directories", B1018),"Listen"), "")
I want to be able to arrow over to that cell and hit a button to play the file (which is contained in cell B1018 in the above example), and there doesn't seem to be a keyboard shortcut to do this.
Move Between Input Cells By Keyboard
There are a series of input cells in a protected worksheet and when the user hits an enter, tab or arrow key, the selection cycles through them in order. The cycle also restarts when the mouse clicks one of the cells out of order. After I switch worksheets and return to the first worksheet with the code, first it says there is a "Compile Error: Sub or Function Not Defined" and the code "Call SetKeys" in Sheet1 is hilighted. When I ignore that and stop the debugger, there is a "Run-time error '13' Type mismatch" and the code line "If arrIndex > 0 And arrIndex < UBound(arr) Then" is highlighted.
My question is: why doesn't the code work after I switch worksheets? especially given that it works perfectly before I switch worksheets. (ie the file opens on sheet 1 and everything is perfect, click sheet 2 tab and back to sheet 1 - nothing works) I also have yet to figure out why it seems to be necessary to post the same code in both ThisWorkbook and Sheet1.
Private Sub Workbook_Open()
'This code allows the user to change the unlocked cells, but protects each worksheet
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="mypassword", _
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!
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.
Cash Register With Keyboard Numerical Keypad
With reference to Cash Register example that I downloaded from Roy Cox; is it possible to link the numerical keypad that is created in the file to the PC Keypad.
I realise I would need to use keypress functions but am unaware of the ascii for these keys. Also; how do I limit that the keypad writes it to the label? Otherwise, would I need to create a new userform instead of what was created by Roy.
Scrollbar Work The Same Way As The Keyboard Arror Keys
Using a listbox with no drop down, just 1 item visible, click to select an item - it highlights in blue. Then use the up/down keyboard keys and the next item above/below becomes visible and is blue - i.e. is selected. However scrolling using the control sidebar scrolls the list but does not select the displayed item. How can I make the scrollbar work the same way as the keyboard arror keys? Alternatively - how can I check that the displayed item is the selected item
(Its not multi-select)