Posts by gmccreedy

I recently upgraded from MS Office 2003 to MS Office 2010. I have tried to load the XlXtrFun.xll add-in for Excel 2010 since I need it for alot of my older spread sheats to work, and for some reason it won't let me.

It keeps prompting me that its not a valid add-in. I have redownloaded the file from the site and its still not working.

http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm

Is there something I need to do to make this load properly or is it now obsolete?

Any help is much appreaciated.

Re: Right Click Insert Row Command Disabled

Not sure why that didn't work, but I added the "ROW" designation to this command and it worked fine.

Public Sub Menu_Reset()
CommandBars("row").Reset
End Sub

Operating System: Windows XP Professional

MS Office Version: 2010

I have a small issue with two computers in my office that cannot re-enable the "Insert Row" command on the right click menu.

What is strange is the the "insert sheet rows" on the Home-Cells ribbon works fine.

I have tried running the following code to reset the issue, but to no avail.

Sub ResetCellMenus()
    Dim cbr As CommandBar
    For Each cbr In Application.CommandBars
        If cbr.Name = "Cell" Then cbr.Reset
    Next cbr
End Sub

Any assistance is greatly appreciated.
For what its worth, the Insert Column Command does work.

This has been cross posted from MrExcel Forum: http://www.mrexcel.com/forum/showthread.php?t=525344

Re: Load and Unload of Addin Error

To not leave this issue hanging, I resolved it by "opening" the addin as opposed to having it preloaded and installing on opening. Still not sure why there is such an issue with the standard "installed = False or True" but its just to unstable to continue with.

Private Sub Workbook_Open()
Workbooks.Open Filename:="P:\Timesheets\Template\Module Standard\BE.xlam"
Application.Run ("BE.xlam!WBopen")
End Sub

and

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run ("BE.xlam!WBbeforeclose")
On Error Resume Next
Workbooks("BE.xlam").Close False
End Sub

Re: Load and Unload of Addin Error

This is very odd. I have gotten it to work randomly, without revising the code either. It somehow "times out" or somehow convinces itself that the addin is installed, yet its not.

Hello All,

Its been sometime since I have been involved here and I have an issue with the loading and unloading of an addin I think.

The code is fired on the host template file to load the add-in as such.

Private Sub Workbook_Open()
AddIns("Be").Installed = True
Application.Run ("BE.xlam!WBopen")
End Sub

And subsequently unload the application on closing of the document as such:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run ("BE.xlam!WBbeforeclose")
AddIns("Be").Installed = False
End Sub

Once it passes into the application it hangs up at the first statement with a "Run-Time Error '9' Subscript out of Range" error.

If I disable the load and unload features and just leave the addin installed, I have no issues and everything runs fine. It appears that once I have the installed false and true feature, it just hangs up.

Any thoughts to this? (If I use On Error Resume Next throughout all the code, it ultimatly works, but I am not sure if that is an appropriate solution).

Re: Add Row On Unmatched Condition

bradles,

that is an excellent idea...and i was already pondering something like that. But actually, like i stated in the original post, this is only run once a month, so I just may suck it up and do it myself so no one can "f" it up. I may just do it with an On-Time method at night while no one is around and just leave my computer running, that way it can take as long as it wants...

i was hoping to be able to allow the office manager to do this (who is not, shall we say, savvy). Looks like another "micro managing" situation...

o well. If you guys think of ANYTHING though...please let me know!!

Again, thanks for all your help...

Re: Add Row On Unmatched Condition

thanks guys for trying...if its the fastest its going to get then so be it. takes about a minute and a half on my computer to run because of the size of the list i guess. my computer is not exactly "slow" either...

thanks again.

I have a summary list of all projects that my employees work on over the course of the month. Its gets sorted by date and project and is a pretty extensive list.

My goal is simple...to search through the list and add a blank row between each project.

I have written this code, that does work, but it takes quite some time to execute and I know there has to be a better way.

Can you guys take a look at it and see if there is more efficient way to do this?

Dim myarray As Variant
Dim cell As Range
Dim mycount As Integer
Dim i As Integer


Set myarray = Worksheets("summary").Range(Range("b2"), Range("B65536").End(xlUp))
Set cell = Range("b2")
mycount = myarray.Count


For i = 0 To mycount
    If cell.Offset(i, 0).Value <> cell.Offset(i + 1, 0).Value Then
            Range(cell.Offset(i + 1, -1), cell.Offset(i + 1, 3)).Insert shift:=xlDown
            i = i + 1
            mycount = mycount + 1
    End If
Next i

Thanks in advance!!

Re: Combobox Not Updating

Doevent did not help. All Combo boxes are embedded. i am just going to bang my head off the desk for a while...

Re: Combobox Not Updating

thanks to all. Going to try both checking if embedded, then run the code on one of the combo boxes not working. There definetly may be corrupt things going on here. The sheets have crashed before, but this would be a nightmare if it is.

Its the strangest thing, because it works, then doesn't work, then works, etc. Never seen anything like it.

Will post back with results.

thanks for all your help everyone!!

Re: Combobox Not Updating

i hope its not code that i need...I have 12 sheets with about 80 combo boxes...

Re: Combobox Not Updating

Any thoughts on this?

Re: Combobox Not Updating

yes sir. Those are them. NOT on a userform though...in the worksheets themselves.

I have hundreds of combobox's that provide a list of names (few thousand names).

I have it set up so a user can select anyone from the combobox for the line of data they are working on.

The problem is, if I add data to the list that is populated by the combo box, the combo box doesn't always update.

the combo box link to the data by named range (list on another sheet in same workbook), and the named range is automatically updated when an entry is added (so the range is correct), but the combo boxes still won't update all the time. The strange part is...sometimes they do. Very weird.

Any suggestions?

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Quote from tosharan

I Agree that sir... i am sorry if i have written something wrong...i will surely study that.. however if you could get me the actual code... i would be greatfull to you..

thanks
Sharan

He gave you the code in the example workbook attached to his post...that is the only code he is going to give to you because he doesn't have time to DO IT FOR YOU.

So...like you said..."i will surely study that"...well...maybe you should STUDY the code he attached for you as an example...

Re: Load Custom Functions In an Add-in

Thanks for the reply Dave, however, i am still stuck here. I am probably missing something simple.

I have tried this many ways now and am not sure how to resolve this.

First thing is, all my custom functions DO work and they are in the "All" category. And when called from the add-in, they work fine. (The add-in is never uninstalled on the user computers, so they can access these functions whenever they need).

The custom category however is the problem. I see that there is specific mention to the fact that the "custom category" will delete after creation, thus the workbook open event would seem logical to reinitialize the loop code that will replace all the functions back to the cusomt category. This doesn;t seem logical unless I put a workbook open event in each employee's "personal workbook", which I would like to avoid.

I tried doing "How to add a new category" by inserting a MS Excel 4.0 Macro sheet, and this appears to work. However, I appear when I try to add functions to the custom category, they are not retaining their code.

I tried defining a name for each, then adding the code via VBA, but this ends up with redundant functions, one working, one not working (until you reopen, then the one that works dissapears...).

Am I doing something complety wrong here? I have gone through all the steps from this site time and time again and am still failing...

I have created some user defined functions and have associated them to a custom category.

To simplify things, I wanted to add these functions and the custom category macro to an Addin I already have installed for all my employees.

How do I get the the custom category macro to excecute? It should run as soon as excel is opened...but I am not sure what event should be used and where is it installed?

Any suggestions?

Re: Define Data Range, Given Cell Address To End Row

apologies, i didn't look at the script close enough to realize it was a function. What are you trying to do...post the range address as the answer? I am not sure if you can do this. I have never seen a dynamic range used in a function.
perhaps the "higher ups" have your solution.

sorry again.

Re: Define Data Range, Given Cell Address To End Row

you haven't defined "strDataStart"

try...

Dim strDataStartAs Range 
     
     ' Define first cell
    strDataStart= activecell