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
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 ?
I'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'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?
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")
I 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
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?
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".
I 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.
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.
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.
i have a list of about 20,000 phone numbers that I need to edit.
all start 01 123456 01 123457 01 123458 etc
I am trying to create a macro that will change them to
+001123456 +001123457 +001123458 Etc
However, no matter what I try it always comes out as
+001123456 +001123456 +001123456
F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.
this is my first time creating macros so am completly lost. I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.
I have a user form with a TextBox (TextBox1), the user enters a filename and presses CommandButton1, this then opens the file. I have set an error trap so that if the file does not exist a MsgBox prompts the user to re-enter. So far so good, however if the error trap operates any further files entered are declared as non-existent even if I know they exist. I thought this would be quite easy but once again VBA bites me back!!
here is my code
Sub CommandButton1_Click()
stPath = ThisWorkbook.Path stFilename = "Retention" & "" & TextBox1 & ".xls" stFullname = stPath & "" & stFilename 'completes full path of for TextBox1
On Error Goto Errorhandler: 'go to this if file does not exist Workbooks.Open stFullname ActiveWorkbook.RunAutoMacros xlAutoOpen
I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1"
I've been trying to figure out ways to edit the VBA in "Module 1" to make this work
Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.