Posts by robnot

Re: Override Default Macro Hotkey

Setting up a hotkey as such:

Application.MacroOptions Macro:="MacroName", ShortcutKey:="U"

will assign the macro to Ctr+Shift+U rather than Ctr+U. I was wondering if there was a way to cut through the additional Shift.

What I mean to do is to make the user experience with this particular sheet as close to normal Excel usage as possible, even though almost all actions the user takes will trigger macros to do all of the real work behind the scenes (a lot of verification and permissions, database loading, and chunking of operations) and there are a lot of things the user will simply not be allowed to do.

Is there a way to set up a macro hotkey from within VBA so that I can override a built-in "Ctr+" option (like Ctr+C, Ctr+U, Ctr+Z)?

Is there a structure inside of Excel which keeps track of all the actions taken by a user (something that would presumably be used to allow for an undo sequence)? If so, is there a way to get access to it?

Re: Events for Active Workbook Via Add-in

Thanks for the reply, Dave. I wasn't sure if I was going to get anything for this one. The App_SheetSelectionChange event is exactly what I need.

As an aside, I know it's possible to insert and remove VBA modules directly within a workbook, but do you know of any cases where it might actually be necessary? I was leaning that way for this problem before your response.

Is there a way to set up a cell selection event trigger through an addin when it is installed so that I can respond to selections made on user's sheets?

Re: To Find The Address Of The Last Cell Of A Range Defined In Vba?

Ah, that's clever. Thanks.

In several places in my code I need to define ranges and save them in Application.Names. I need to be able to reference the last row and column in that range but I don't know how. Is there a reference within the Range object for the boundaries used to create it?

ie, the B3 in Range("A1:B3")

Not all of the ranges will have content in them and many of them will overlap, so looking for the last non-blank cell won't work.

thanks,
Rob,

Re: Run Addin Code On Other Workbook's Opening

That link was exactly what I needed, thanks so much. Now I've got a new bag of tricks.

I want to be able to launch code in an add-in to perform a check when other workbooks are opened by the user. Code in the Open event for the add-in launches when Excel opens, not when each workbook is opened. Does anyone know how I can do this?

thanks in advance,
Rob

Re: Getelementsbytagname Not Returning Anything

That's a good point... have you actually looked at the HTML for the particular page which is failing to confirm that the input tags are the ones you're looking for?

Re: Getelementsbytagname Not Returning Anything

...
[B]On Error GoTo catch[/B]
    With objIE 
        .Visible = True 
        .navigate "https://www.citizensbankonline.com/" 
         
...
            End With 
[B]Exit Sub
catch:
    MsgBox CStr(Error)[/B]
End Sub

Now you should be able to see exactly what error is occuring.

Re: Not a Valid Add-in Error

Both mine and their versions of Excel are 2003 (11.8142.8132, SP2). My profile says 2007, but I really have both on two different machines.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Ugh... found out that IT has inconsistently set the security for Add-ins across different user's machines. Some of us can freely install add-ins, others can't.

I created an add-in for Excel 2003 by following your site's instructions, substituting my own subroutine's name for "MyGreatMacro" (for the purposes on testing all it does is spit out a MsgBox). I then put the resulting xla file on a network drive and went through the standard process for installing an Addin :

Tools -> Addins -> Browse for the file and hit ok, check it off, etc

This works perfectly fine when i try to install on my own machine, but when I try on other user's machines I get an error message stating that the xla file is not a valid Addin. Excel's error message in this case is about as unhelpful as they come. Does anyone know what can cause an addin to produce this error message?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]also posted here

A quick question about Add-ins: when I add an Add-in to Excel on my machine from a remote server, does it make a copy of the worksheet and macro or just reference the Add-in's workbook?

I want to use Add-ins to distribute code to users throughout my organization and I need to know if new versions will have to be "re-installed" by the users or changes to the code on the server version will automatically propagate.

Thanks,
Rob

Re: Auto Re-assign Macros To Command Buttons

You could probably set all of the assignments in the Workbook Open event of the Personal.xls workbook.

Re: Error - Object Required - Adodb Parameters

Removing the parentheses did the trick. I don't really understand why, though.

Edit: Ugh, I get it now... An equivalent solution would be to put a Call in front of the Append statement. Sometimes VBA really gets under my skin with this syntax.

Thanks so much for the help, Mavyak

Re: Error - Object Required - Adodb Parameters

Now also posted on ExcelTip

I'm using the following code and getting an "Object Required" error on the cmd.Parameters.Append line of code. I can't for the life of me figure out why. Can anyone help?

Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim records As ADODB.Recordset
    Dim par As ADODB.Parameter
    
    Set conn = New ADODB.Connection
    conn.ConnectionString = "..."
    conn.Open
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "..."
    cmd.CommandType = adCmdStoredProc
    Set par = cmd.CreateParameter("@p", adInteger, adParamOutput, 10, 0)
    [B]cmd.Parameters.Append (par)[/B]


    Set records = New ADODB.Recordset
    Set records = cmd.Execute

Re: Close Application Window With Macro

Thanks, it works perfectly!

I'm running into a problem when I'm using the Application.ActiveWorkbook.Close method within the Workbook_Open event. The workbook opens and closes, but Excel itself doesn't close, even though there are no more workbooks open. Is there a way to ensure that Excel closes?

thanks,
Rob