Assigning keyboard shortcut to VBA macro programmatically

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - VBA and Excel - If and Find expressions Hello I am trying to use an If statement to find an piece of text and then go to the cell that contains that text. I have, as part of the if statement, an else statement that just cancels the expression and does something else if it cant find the iece of text. However, the expression doesnt seem to work when VB cant find any cell with the specific text and it just comes back with a run time error. Does anyone know if there is a proper way of putting a "find" expression inside an "if" expression or maybe a better way of doing what I am trying to do Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 2
    - 3 consecutive periods = 1 character I seem to have stopped it auto correcting, but how do I get the ellipsis auto correct back The autocorrect menus are not easy to follow. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 3
    - protect cell containts Hello, I have office xp . i am not well versed with visual basic in excel programming. i have one sheet where i have created a macro which returns current system date by pressing ctrl + o i need two things 1) i have 15 lines in A column wich contains list box . user have to select a option from that list box and next to that option by pressing "ctrl + o" record the system date. onces macro is run , the time recorded can not be edited. 2) same as above i have another 15 lines on the same sheet where user selects the option from the list box and record the time next to that option by manually entering time. user can only select a option from that list box and onces selected can't change that option. user can not delete that list box . user can not delete time recorded either by macro or manual entry. list box by macro option1 8:00 AM 10:00 AM option2 12:00 AM 4:00 AM option3 8:00 AM 8:00 AM list box by manual entry option1 8:00 AM 10:00 AM option2 12:00 AM 4:00 AM option3 8:00 AM 8:00 AM i am trying this stuff , but getting different errors one of that is "sub not defined" can any one give me solution on this... Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 4
    - adding a non-adjacent cell to a named/defined range I have a set of data in a spreadsheet that I want to run a macro through that will: 1. test each cell against specified conditions; 2. depending on what conditions are met, add that cell to a specific, named range; and 3. run statistical analysis on the named ranges. #1 and #3 I am OK on, but #2 - I have not been able to figure out how to resize the range to add a specific, non-adjacent cell. Hope this is understandable. Many thanks in advance. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 5
    - Creating Excel File Error Hello, I'm trying to create an Excel file using ADO. I can read from an Excel just fine, but creating it has been more of a problem. When I try to open a connection to a file name, I get "object not found" error. According to the MS article I read, the workbook should be created automatically when I create my first table in the workbook. However, I can't see how I can do this if the spreadsheet has to exist first. I tried to create a blank file using the fileInfo.Create() mehtod, but this didn't solve the problem (I just got a different error about the file format being incorrect). Therefore, how do I use ADO to create a brand new workbook that does not yet exist Am I doing things in the wrong order Thanks, Michael Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 6
    - Using Inet to download an FTP File Hi Dudeman Think about spaces in LocalFileName. The GET command accept only two parameters. If it find space in the second parameter, it will transform it as new parameters. Test it : .Execute , "Get " + RemoteFileName + " " + """" + LocalFileName + """" Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 7
    - Creating and Editing Microsoft Access Reports using Visual Basic Thanks for your help guys Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 8
    - WER Dialog not showing up on Vista Hi Jerry, Managed crashes are handled by the CLR (CLR2.0 reports are exposed through Winqual). If your managed application is running on Windows Vista, then the CLR hands off the crash to WER so that the Operating System's opt-in model can be followed as well as any OS specific reporting policies. You don't need to do anything special in your application to get this to work, but I recall hearing about a special case with WinForm applications. Is this a WinForm App For your second question ...If you want to take advantage of the Vista WER APIs for additional data collection, you need to use PInvoke. You do not need to use WerReportCreate or WerSubmitReport. The additional information will automatically be submitted with the crash report. Thanks, -Jason Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 9
    - Add additional value axis gridline to msGraph.Chart.8 object MS Access report with embedded msGraph.Chart.8 object. Graphing the series based on the rowsource to my satisfaction, but would like to add a pass/fail reference line to illustrate passing/failing datapoints. Can I simply add a value axis gridline with the appropriate value and a custom color to illustrate the pass/fail reference line If not - can I fake doing that by plotting another series of data with a constant value = my pass/fail point Would appreciate any code snippets or pointers to potential examples that would be of assistance. Good info on msGraph.Chart.8 seems difficult to come by :-) Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 10
    - A question from a beginner... Here is a another good starting point for your VBA journey. http://www.mvps.org/dmcritchie/excel/excel.htm#vbatutorials Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 11
    - email using CDO (Collaboration Data Objects)? What's a long time seconds, minutes, hours or days before email arrives. Does the email get delivered to the recipient directly or do they have to log on to a system first How often does the recipient check for email Is there a delay with every email or was this a one of Did/does the ISP have problems with emails There are many points along the emails journey where delay could be incurred. Some of which you may be able to control. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 12
    - Help needed: Retrieve tables in word and copy them to Excel Code Block a = 1 For Each tTable In ActiveDocument.Tables tTable.Range.Copy With ExcelWB.Worksheets(1) .Paste Destination:=.Cells(a, 1) End With a = a + tTable.Rows.Count + 2 Next Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 13
    - Creating LookUp/Filter Command Button In Access 2003 using VB This line doesn't look right: Me.Filter = "Last Name = "" & me.Last_Name & "'" You have an odd number of double quotes, and also an odd number of single quotes. Should the second double quote be a single quote Like this: Me.Filter = "Last Name = ' " & me.Last_Name & "'" Also, you said the field names were both "Last Name" (with a space). Your code has Last_Name (with an underscore). If it really is a space, try this: Me.Filter = "Last Name = '" & me.[Last Name] & "'" Note that the square brackets [] force it to treat the contents as a field or table name - without them the space would make Last Name be treated as two separate words. You may also need the brackets around the first Last Name (even though it's inside the quotes). Putting brackets around field names is a good habit to get into. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 14
    - Office 10 \ winword.exe runtime error Hello I have been having exactly the same problem and needed to urgently use my powerpoint presentation and modify it ASAP I spent all day trying to sort the problem however when I uninstalled the 2003 powerpptwiz etc & powerpoint immediately worked. No more downloads for me! Have a great day I am excited now I can finish my presentation although I have wasted many hours searching for a solution. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 15
    - Trying to use XML Schema to import datas Hello all! well, I've been trying to importing some datas of a XML document based in rules XML schema to an Acess Table. What I found about it was only the code Application. ImportXML _ DataSource:="employees. xml ", _ ImportOptions:=acStructureAndData then I used this Application.ImportXML _ DataSource:="d:\schema\users.xml", _ SchemaSource:="d:\schema\users.xsd", _ DataTarget:="Users_Main" No sucess. I have no idea how to do this!! any help is appreciate... ThanX all Marcos Hercules Santos Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 16
    - Hiding Access Application Window Palaemon, Wierd! I'm sure I replied to your question, but it appears to have been removed. Obviously bad advice. If you tap ShowWindow into Google, there's loads of information about this API, including code snippets and various implementations. Good luck! Tag: Assigning keyboard shortcut to VBA macro programmatically
- Next
  - 1
    - Browse hard disk with VBA Hello, I want to read a text file into Excel with VBA. This all works fine with some code I found, the only problem is that the text file I want to open should either be written in the code, or in a textbox but than I have to type the complete location (C:\MyDocuments....) what I would like to have is a browser, similar to the one from Windows on "Ctrl O", which opens when I push a button so I can browse for the file on my hard disk and select the file I want to read into Excel. Does anybody know if this could be made in VBA, and if it is possible how Barry Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 2
    - Microsoft Document Image Printer - MODI Registry Settings Setting PrivateFlags to 1 will make Note.mdi be "printed" in the current default folder. PrivateFlags of 17 will make Note.tif print in the default folder. On TIFDPI I've also seen 600 and 2400 work. Here's some C# code for playing with it, CoverLetter is a CrystalReport report. private CoverLetter CoverLetter1; private int iMODICOMPIMGEMF; private string sMODIPath; private int iMODIPrivateFlags; private int iMODIPublic_Orientation; private int iMODIPublic_PaperSize; private int iMODITIFDPI; private int iMODIOpenInMODI; private string sMODIKey = @"HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\MODI\MDI writer"; private void SaveMODIKeys() { iMODICOMPIMGEMF = (int) Microsoft.Win32.Registry.GetValue(sMODIKey, "COMPIMGEMF", 0); sMODIPath = (string) Microsoft.Win32.Registry.GetValue(sMODIKey, "DefaultFolder", @"C:\"); iMODIPrivateFlags = (int) Microsoft.Win32.Registry.GetValue(sMODIKey, "PrivateFlags", 0); iMODIPublic_Orientation = (int)Microsoft.Win32.Registry.GetValue(sMODIKey, "Public_Orientation", 1); iMODIPublic_PaperSize = (int)Microsoft.Win32.Registry.GetValue(sMODIKey, "Public_PaperSize", 1); iMODITIFDPI = (int) Microsoft.Win32.Registry.GetValue(sMODIKey, "TIFDPI", 300); iMODIOpenInMODI = (int)Microsoft.Win32.Registry.GetValue(sMODIKey, "OpenInMODI", 1); } private void SetMyMODIKeys() { Microsoft.Win32.Registry.SetValue(sMODIKey, "COMPIMGEMF", 1); Microsoft.Win32.Registry.SetValue(sMODIKey, "DefaultFolder", @"C:\temp"); Microsoft.Win32.Registry.SetValue(sMODIKey, "PrivateFlags", 17); Microsoft.Win32.Registry.SetValue(sMODIKey, "Public_PaperSize", 1); Microsoft.Win32.Registry.SetValue(sMODIKey, "Public_Orientation", 1); Microsoft.Win32.Registry.SetValue(sMODIKey, "TIFDPI", 300); Microsoft.Win32.Registry.SetValue(sMODIKey, "OpenInMODI", 0); } private void RestoreMODIKeys() { Microsoft.Win32.Registry.SetValue(sMODIKey, "COMPIMGEMF", iMODICOMPIMGEMF); Microsoft.Win32.Registry.SetValue(sMODIKey, "DefaultFolder", sMODIPath); Microsoft.Win32.Registry.SetValue(sMODIKey, "Public_Orientation", iMODIPrivateFlags); Microsoft.Win32.Registry.SetValue(sMODIKey, "Public_PaperSize", iMODIPublic_Orientation); Microsoft.Win32.Registry.SetValue(sMODIKey, "Public_PaperSize", iMODIPublic_PaperSize); Microsoft.Win32.Registry.SetValue(sMODIKey, "TIFDPI", iMODITIFDPI); Microsoft.Win32.Registry.SetValue(sMODIKey, "OpenInMODI", iMODIOpenInMODI); } private void btnPrint_Click(object sender, EventArgs e) { try { SaveMODIKeys(); SetMyMODIKeys(); CoverLetter1.PrintOptions.PrinterName = "Microsoft Office Document Image Writer"; CoverLetter1.PrintToPrinter(1, false, 1, 1); // Note.tif will now be in c:\temp . If Note.tif already existed, the MODI printer adds numbers: Note0001.tif and up. } catch { } finally { RestoreMODIKeys(); } } Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 3
    - How to open a folder through excel macro! i could get it somehow, try this, its very simple. Shell "explorer.exe " & folderpath, vbNormalFocus Amps Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 4
    - Application-defined or object-defined error Hi Joe, to throw an exception, you can use Err.Raise method. In the caller procedure, you can use Exit Sub to stop the processing. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 5
    - Subscript out of range Hey , Sheets( " bw_sh " ).Copy After:=ThisWorkbook.Sheets(3) Best Regards Cathrine Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 6
    - Application.FileSearch in Excel 2007 Hi I went back to using VBScript to get the job done here is a snippet Function ReportFileStatus(filespec) Dim fso, msg Set fso = CreateObject("Scripting.FileSystemObject") If ( fso.FileExists( filespec ) ) Then msg = filespec & " exists." Else msg = filespec & " doesn't exist." End If ReportFileStatus = msg End Function this should do the job for you... ofcourse with a bit of modification. Make sure that you save the file in XLS format rather than the new XLSX format. Glax Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 7
    - Can you use VBA to access a spread sheet embedded in a word doc? The primary verb is to activate, same as clicking the object. You can use that but there is no code to deactivate the object when completed. Have not tried using verbs with a msoEmbeddedOLEObject, so can not say. Did you try Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 8
    - Excel - Resize image to specified target cells' dimensions Thanks bi-lya, I'm going to give it a try after lunch. As far as the single versus multiple buttons... The company I work for is ISO and this workbook is being made per specifications mandated in a procedure, every instance of a range to include an image must have it's own command button. I don't make the rules here, I just get paid if I follow them! Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 9
    - problem with pivot table Visible property in excel 2003 SP2 vba Hi there, I have problem with pivot table Visible property in excel 2003 SP2 vba."run-time error 1004: Unable to Set Visible Property" VBA code have a Pivot Item property for filtering pivot data. Pivot item is "DeliveryDay". In code i check that Visible value of items (true/false) and if it is False then i like to change it to True. Code using For Each loop for checking Visible proberty value. (If PivItem.Visible = False Then PivItem.Visible = True) I'am really sure that i have right syntax in my code. I'am using similar code in other PivotSheet and it's works :-| Following article tells at that is an Excel bug. I don't have any sorting in pivotTable. Article is over one year old, so i loaded SP2 for this case -> No help. Is there any hotfix/solution for this case "Is an Excel bug that set PivotItem.Visible to true will generate 1004 Macro Error http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=168090&SiteID=1 " cheers, Jari Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 10
    - Subscript out of range with a simple sql :-( [newbie to VBA] Iam getting the exact same problem. I just started in VBA last week. I am trying to learn from a problem. This is an already built code and iam learning. The code works fine when i open following FOUR files: 1) 2263A1.txt 2) 2263A1(version 1).txt 3) 2263A1(version 2).txt 4) 2263A2.txt The following 2 files contain DATA and some Macros. 5) Data Arrangement 2.3- 04-26-07 6) LN STD Ct Iam getting problem "Run-time error 91 Subscript Out of Range" and the debugger points me to the line with the * and marked in RED whenever I open only one file i.e. "2263A1.txt" instead of opening all 4 (1 through 4) 'find the first workbook of data and activate it For i = 1 To Application.Workbooks.Count Step 1 If InStr(Application.Workbooks(i).Name, "Data Arrangement") = 0 And InStr(Application.Workbooks(i).Name, "Macro") = 0 _ And InStr(Application.Workbooks(i).Name, "STDCt") = 0 Then firstbook = i Exit For End If Next If doFourPlates Or doTwoPlates Then * Application.Workbooks(firstbook - 1 + wb).Activate End If If StrComp(Worksheets(1).Range("A1"), "SDS 2.3") = 0 Then Adjust End If Please let me know how to fix or atleast give me some directions. Thanks and much appreciated. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 11
    - Run-time error '13': Type Mismatch *ignore this ^^!* i always get this error when i try to run...does anyone know what's the problem Code Snippet Option Explicit Private Sub UserForm_Initialize() UpdateData cbxDept.AddItem "Marketing & Sales" cbxDept.AddItem "Supply Chain" cbxDept.AddItem "Purchasing" cbxDept.AddItem "Technical & Production" cbxDept.AddItem "Research & Development" cbxDept.AddItem "Finance & Control" cbxDept.AddItem "Human Resource" cbxDept.AddItem "Globe & ISIT Services" End Sub Private Sub cmdPrevious_Click() If txtRow.Text > 1 Then txtRow.Velue = txtRow.Value - 1 UpdateData End Sub Private Sub cmdNext_Click() If txtRow.Text < Range("Database").Rows.Count Then txtRow.Value = txtRow.Value + 1 UpdateData End Sub Private Sub txtFName_AfterUpdate() Range("A2")(txtRow.Text).Value = txtFName.Value End Sub Private Sub txtLName_AfterUpdate() Range("B2")(txtRow.Text).Value = txtLastName.Value End Sub Private Sub cbxDept_AfterUpdate() Range("C2")(txtRow.Text).Value = cbxDept.Value End Sub Private Sub txtRow_AfterUpdate() If txtRow.Text < 1 Then txtRow.Text = 1 ElseIf txtRow.Text > Range("Database").Rows.Count Then txtRow.Text = Range("Database").Rows.Count End If UpdateData txtFName.SetFocus End Sub Private Sub UpdateData() txtFName.Text = Range("A2")(txtRow.Text).Value txtLName.Text = Range("B2")(txtRow.Text).Value cbxDept.DropButtonStyle = Range("C2")(txtRow.Text).Value End Sub thanx Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 12
    - Microsoft Office Document Image Writer driver Hi, When I first saw the MS Office Document Image Writer in the printers box, I did'nt know what it was for. When I started downloading crosswords, etc., for my mom, I found it was helpful to print to the MS Writer for the OCR, and then copy and paste to MS Word. Then it went away, and I couldn't get it back until I found your article, which took a week or so. Thankyou so much. Respectfully, Joe Indian Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 13
    - CRM 3.0 Outlook Client - Missing Price List Go back to basics Are you having any problems using CRM in a web client =============================== John O'Donnell Microsoft CRM MVP Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 14
    - Problem: Macro not deleting columns I select Strange indeed ;) If you want to email your file I will take a look see. My profile has a web address that contains a usable contact email. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 15
    - Using VBA in Word to slect and print a particular Record (recipient) I hoped the recorded macro might show something more useful. The FindRecord method might do what you need: ActiveDocument.MailMerge.DataSource. FindRecord( FindText , Field ) For more info, and an example, see the following: http://msdn2.microsoft.com/en-us/library/aa172022(office.11).aspx Good luck. Tag: Assigning keyboard shortcut to VBA macro programmatically
  - 16
    - sending emails from MS Word with variable subject line You can make use of the DataObject to get text from clipboard. Sub x() ' requries reference to MS Forms library, which can be done by simply adding a userform Dim objDO As MSForms.DataObject Set objDO = New DataObject objDO.GetFromClipboard ' not sure of your code for getting email object Email.Subject = objDO.GetText End Sub Tag: Assigning keyboard shortcut to VBA macro programmatically

Ad Brandt

Using OLE, I create an Excel sheet with graphs and some macros to manipulate them. I'd like to assign keyboard shotcuts to the macros, so that the user has easy access to them. I can't figure out - however - how to do that. Any suggestions Thanks in advance.

Top

Re: Assigning keyboard shortcut to VBA macro programmatically

ADG

Using the macro recorder in Excel often gives an answer. I assigned Ctr+Shift+F to macro1 and recorded this as a macro. results are below:

Application.MacroOptions Macro:="Macro1", Description:= _
"Macro recorded 12/06/2007 by Tony Green", ShortcutKey:="F"

Top

Re: Assigning keyboard shortcut to VBA macro programmatically

Shasur

You can use Application.Onkey to set the key for the macro

Here you have the sample code and commonly used keys : http://vbadud.blogspot.com/2007/06/assigning-shortcut-keys-excel-macros.html

Cheers

Shasur

Top

Re: Assigning keyboard shortcut to VBA macro programmatically

Ad Brandt

Thanks, that works!

Top