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 ?
View 3 RepliesI've created Keyboard Shortcuts to run Macros in "Workbook A". Problem is, if I have "Workbook B" open in the same Excel instance/process as "Workbook A" the Keyboard Shortcuts will run the Macros in "Workbook A".
Is there a way to restrict the Keyboard Shortcuts to only run the Macro if Workbook A is active?
Or is there a way to code the Macro to run its procedures only if Workbook A is active?
I have a routine that by clicking one button, that calls a macro, that currently opens Excel, or Word, or WordPerfect. The following macro uses a Case Statement looking at what the extension is, such as for Excel . . . xls
I have added a case statement for a shortcut . . . exe
How can I create keyboard shortcuts in the easiest way.
E.g.: I would like to create my own shortcut for "Merge".
unknowingly i must have deleted some shortcuts via VBA code.
now, the shortcut key (excel 2003) Alt+Insert+Row works!
But, Alt+Insert+Column does not! this works in my colleagues computers which also have Excel 2010.
is there a way to reset all keyboard shortcuts on my computer? i have tried in vain to find a solution but not found one option.
My macro essentially requires a deletion of the entire sheet, so I cannot run it through a button, it'd by definition be deleted. So I was thinking to run this macro through the keyboard, how to assign a keyboard key to existing macro?
View 4 Replies View RelatedMacro2 is in ThisWorkbook.
Code:
Application.MacroOptions Macro:="Macro2", Description:="", ShortcutKey:= _
"d"
How to do the above when this workbook is not active workbook.
How can i add a keyboard shortcut in already saved macro. Is there any way to do it or need to write a new macro?
View 2 Replies View RelatedI have two sheets that contain data and I have to switch between them all the time, and I thought I could make it easy by assigning the "switch" to a macro. I am now looking to assign that macro so a single button, like the "`" key. I have tried various pieces of code but none of them worked.I think something like the below may work but I don't know how to start.
[Code]....
If Sheets("Sheet2") is active, then call switch 1, to switch back.
I used keypress code in one of forms, after running following command non of my keyboard is working in excel. When I am restarting excel everything is fine. what could be the reason?
View 7 Replies View RelatedHowever, I have not been able to find a solution. I created 2 macros that work using the alt+F8 and enter option as well as the run from the macros menu method. Whenever I try to use the keyboard short cut eg. Ctrl+e it will either not do anything or pause and then select every cell. The codes are below:
Sub Macro5()
'
' Macro5 Macro
'
[Code]....
I have tried multiple letter options and have also tried many macros in a new workbook and I cannot get it to run with the keyboard shortcut.
I would like to replace the keyboard shortcut for the macro dialog box
The standard Alt + F8 is on the right side of the keyboard
I would like somethine of the left side such as Alt + Q
Anyone know if you can place a shortcut on the desktop using VBA code?
View 9 Replies View RelatedI've created a calendar in VB following the instructions in this link - [url]
The calendar works fine but the problem I've got is I've got unwanted copies of the shortcut when I right-click. If you scroll down the URL, right at the bottom in blue is a paragraph that describes the problem and how to get rid of it. I've tried this numerous times but the duplicate entries keep re-appearing.
I've attached my workbook in the hope that any of you experts would be so kind as to take a look and offer any help or advice on how to fix this problem?
Is there a way to find and edit all the shortcuts I created in one (and in more then one) Excel spreadsheet?
View 2 Replies View RelatedShortcuts for moving tabs to different files. A copy of the tab, I mean.
View 1 Replies View RelatedI 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")
I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.
View 10 Replies View RelatedI would like to change an existing Macro……i.e. the current date……which is ,,,,,CNTRL +; …… I want to make it CNTRL + e………..I tried to make my own by running a new macro……but obviously I am doing something bass ackwards…..I tried to look up the current one……that is CNTRL + ; and see how they did it……but couldn’t find that either
View 2 Replies View RelatedI was wondering if there was a macro to add a row to an existing table. The table is on one worksheet and where i wanna enter data is on another.
View 2 Replies View RelatedIs it possible to overwrite an existing .csv file with
The command :
ActiveWorkbook.SaveAs FileName:=fName, FileFormat:=xlCSV
Or does it need to be Killed first ?
Plus do I need any of the options such as Create Backup, AccessMode As etc.
I would like to add a pause to the following macro so that it inserts the word CANCEL 1 second after triggering. I know I need to use the Application.Ontime function but cannot work out how to integrate it.
Code:
Sub Cancel() Application.ScreenUpdating = False
Sheets("Sheet1").Range("T5") = "CANCEL"
Application.ScreenUpdating = True
End Sub
I'd like to create a macro that can access few existing worksheets from different workbooks and print them automatically when i press the button or select them.
Tried userform and listbox that were suggested from other thread, but that didnt work quite well since I'm not quite understand the code behind it.
I have a named range "ABC", range is ("A1", "A3", "A5"). Now I need to use Macro to extend the range to ("A1", "A3", "A5", "A7"), that is, adding "A7" to the existing named range "ABC".
View 1 Replies View RelatedI have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:
Option Explicit
Sub CreateTimeChartData()
Dim vTimeData As Variant
Dim i As Integer
Dim sRoom As String
Dim vLastEndTime As Variant
Dim oSeries As Series
' set up
Application. ScreenUpdating = False
Application.DisplayAlerts = False
' create chart data worksheet
With Worksheets("TimeData"). Range("TimeList"). CurrentRegion
.Sort Key1:="Room", Key2:="Start Time", Header:=xlYes
vTimeData = .Value
Worksheets.Add
On Error Resume Next
Worksheets("ChartData").Delete..........................
Attached is my sample data Sample Data_Combine Sheets.xlsm.
What the code in the attached sheet does is that it combines the data in Sheets Set1 and Set2 into the worksheet named "Combine".
Both input sheets share same columns. Code I use is as below:
VB:
Sub BuildPivotData()
Dim wrsht As Variant
Dim i As Integer
Sheets("Combine").Range("A2:AI500").Clear
wrsht = [{"Set1", "Set2"}]
For i = 1 To UBound(wrsht)
Sheets(wrsht(i)).[a2].CurrentRegion.Offset(1).Copy Sheets("Combine").Range("A65536").End(xlUp)(2)
Next i
End Sub
I want to amend the output in such a way that when the macro is run , in the "Combine" worksheet an additional column is added at the start(col A) with header as "Category".
The category should be nothing but the input Sheet name i.e Set1 or Set2.
Here is an example ... Output Sheet.xlsx.
I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.
These are the instructions I'm currently sending:
In cell n1 enter:
full phone number
in cell m2 enter:
=RIGHT("00000000" & J2,8)
Drag m2 to last record
In cell n2 enter:
=CONCATENATE(61,I2,M2)
Drag n2 to last record
In cell o1 enter:
Date of call
in cell o2 enter:
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))
Just curios if this is the most efficient way to copy a workbook x number of times.
I tried copying 77 workbooks and not sure exactly how long it took, but about 2 mintues. The original workbook is 300 KB.
Is iy possible to create a macro to add a character to a cell with existing text, such as:
Progressive Insurance $
Where the name is in "whatever" font,color,format, and then the macro could insert the $ as pictured above different font,color?
I have played around with trying to write, or copy as image...with no luck.
I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP
Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.