Posts by AJW


    I need to distribute the addin to a wide user group, if I used the Personal.xls file I might overwrite their own existing code.

    "I guess the other thing to check is whether Events are disabled?"

    How do I do this ??

    "All that happens is that Excel locks up when I launch it ?? "

    I've overcome this part of the problem by recoding the Workbook_Open and Workbook_Close events but it still wont trigger.

    "It sounds like a re-installation might be in order."

    No go, work machine and my IT department are already irrate with me and my project. I've been banned from the software directory on the server.

    "Are you sure it's not the code in the Workbook that is causing lock-up? "

    100% OK when manually loaded, problem resides with load through XLStart.

    Thanks again


    OK, quick update.

    I've tried everything I can think of (including digital signing) to get code to trigger with the Workbook_Open event in Excel XP but to no avail.

    If anyone has any idea please let me know ASAP.

    Point of clarrification:

    The Workbook_open event does not work when the file is loaded through the xlstart directory.

    C:\Program Files\Microsoft Office\Office10\XLStart

    It does however work if I manually load the file. The problem is that I need it to work through XLStart as it triggers a series of other addins to be loaded and managed.

    My dumb users gripe about clicking too many buttons let alone having to manually load a file :~(



    Hi All,

    Well I've finally upgraded from good old Excel 97 to Excel XP.

    Must say it looks and feels very pretty ;)

    Slight problem though, I have placed a file in the xlstart directory that has code in the "This Workbook" Module that's meant to run with Workbook_Open. Problem is that it dosen't run.

    I set the security level to low but that didn't work. So as per the recommendations in help I set it to medium which is then supposed to prompt me to allow the code to run, again that didn't work. All that happens is that Excel locks up when I launch it ??

    Anyone got any ideas? I could spend hours trying to figure it out but would appreciate any pointers from people who may have experienced the same problems.

    P.S I haven't played around with digital signatures yet.




    Why not stick with the input box?

    The attached code allows you to select a range that will be used, or as you put it "to prompt the user to paste data into a specific area of the workbook".

    Once the OK is clicked code is returned to and executed on the location of choice.

    Hope this helps.



    Sub MYCellSelect()
    On Error Resume Next
    Dim mycell As Range
    Set mycell = Application.InputBox(prompt:="Select Cell to enter value. ", Title:="Value Location", Type:=8)
    Application.Range(mycell.Address).Value = ("Hello - " & " 100 " & "bonus points")
    End Sub

    Hi all,

    Haven't been around much lately as work has been crazy and I had a nasty car accident that resulted in a fractured back, ouch! Hurts just thinking about it.

    Anyways, back at work now and as usual because I'm the resident XL guru (wink wink) and have developed an in house package that we use I naturally get the blame for any Excel problems that occur.

    I was hoping someone might be able to shed some light on this one.

    People are randomly getting the following message when they attempt to save a workbook on to our server. My thoughts are that the communication to the server has at some time cut out and that Excel can not find the original workbook ???

    Any ideas ???



    Hi All,

    I am creating a workbook (WB1) from sheets in an existing workbook (WB2).

    WB1 is saved to a location of choice by using Application.GetSaveAsFilename, a message box then informs the user of the path to WB1 using Application.ActiveWorkbook.path and WB1 is then closed.

    The problem I have is that I need to somehow capture the file path for WB1 and enter it into WB2 as a hyperlink to WB1.

    Does anyone know how to capture the file path using Application.ActiveWorkbook.path ?

    I need to get the path name for WB1 and record it somehow programatically before I close the file so that I can use it in the hyperlink creation for WB2.

    Any ideas ? Sounds straight forward but I'm stuck !



    Hi Andy & Others,

    Quote: "The reason for the above queries is that if two operations of copy (when programatically this copy macro is called)is done the latest copying action will replace the contents in the common windows clipboard.My aim is to prevent this."

    Specifically: " if two operations of copy (when programatically this copy macro is called)is done the latest copying action will replace the contents in the common windows clipboard.My aim is to prevent this."

    My reading was that gop wanted to stop the common Windows Clipboard contents being replaced.

    I don't know that this can be done so I was suggesting a possible work around to capture the contents before being replaced as a *.clp file and then reload this file to the Clipboard when needed.

    I'm still stuck in Office 97 so I know diddly about the Office specific Clipboard included in 2000+ and XP.

    Hope this clarifies my response.




    I use the following code to launch the clipboard, insert an image as well as format it a bit.

    You may be able to use it to manipulate the clipboard.

    Sub ViewClipboardCurve()
    Dim mycell As Range
    On Error Resume Next
    MyAppID = Shell("C:\WINDOWS\CLIPBRD.EXE", 1)
    MyAppID = Shell("C:\Marketing\Quote Package\Programs\CLIPBRD.EXE", 1)
    On Error GoTo ErrorMessage
    Set mycell = Application.InputBox(prompt:="This will insert the contents of the Clipboard Viewer into the worksheet as an image." & vbCr & vbCr & "Please click on the worksheet to select the location to place the curve." & vbCr & vbCr & "Click the Cancel button if you do not wish to proceed", Title:="Curve Location", Type:=8)
    Application.ScreenUpdating = False
    ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
    Selection.ShapeRange.IncrementLeft 10
    Selection.ShapeRange.IncrementTop 13
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 8
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = 808.5
    Selection.ShapeRange.Width = 570#
    Exit Sub
    MsgBox ("INSERT FAILED - Clipboard does not contain a valid curve image."), vbCritical, "WSG-eQuote"
    MsgBox ("Please ensure that you copy your curve from Perfcurve and try again." & vbCr & vbCr & "This can be done by right mouse clicking on the selected curve, Copy > To Clipboard."), vbInformation, "WSG-eQuote"
    End Sub
    Sub ClipboardViewerClose()
    On Error GoTo error
    AppActivate "Clipboard Viewer"
    SendKeys "%{F4}", True
    Exit Sub
    End Sub

    Also worth noting is that in my version of Clipboard you can save the information copied to the clipboard as a *.clp file.

    If you have specific data you need pasted in from the clipboard then you can programatically send it to the clipboard & possibly save it as a file? In theory you should ten be able to launch Clipboard, Load the file with you data and paste it into the workbook.

    Just some thoughts that may prompt an alternative workaround.




    Originally posted by Dave Hawley
    Out of curiosity, what were the time using the AutoFilter Method? I ask as I use this method frequently and it works on thousands of records in about 1-4 secs.

    Quote "Judges decision is final and no corespondence will be entered into. (but a bribe might change my mind). "

    Sorry Dave but it might upset the winner ;;)

    Only kidding, to be honest it was way faster, probably about 2 or 3 seconds but I didn't set things up for it to work the way the Autofilter seems to...... well my understanding of using it once.

    If time permits I'll play around with it to expand my knowledge but XLDennis's code was quicker to understand and solved my immediate problem of people thinking that their screen had frozen. Curse that Alt/Tab/Delete why are people so impatient ??




    With a RECORD BREAKING performance of 10.64* seconds XLDennis code has shaved a whopping 41.07 seconds off the previous record held by LOOP.

    XLDennis stunned onlookers and officials with the clever use of the FOR / NEXT method in place of the LOOP.

    When interviewed later LOOP lamented his thrashing blaming screenupdating for his demise.

    *Record set on Pentium3, 800MHZ, 128MB RAM, XL97, WIN 98 config. Judges decision is final and no corespondence will be entered into. (but a bribe might change my mind). Precision timing recorded on a Quartz Digital $29.95 Lorus watch from Kmart - Water 50m Resist.

    (Imagine what people can do with all that spare time now) ;;)

    Modified code as follows:

    Sub hideit()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range
    Dim vaData As Variant
    Dim I As Long

    'Set wbBook = ActiveWorkbook 'ThisWorkbook
    'Set wsSheet = wbBook.ActiveSheet '.Worksheets(1)
    Set wbBook = Application.ActiveWorkbook 'ThisWorkbook
    Set wsSheet = wbBook.Application.ActiveSheet '.Worksheets(1)

    With wsSheet
    'Set rnData = .Range("D1:D255")
    Set rnData = Application.Range("EquipSumQty")
    End With

    vaData = rnData.value

    For I = LBound(vaData) To UBound(vaData)
    'If vaData(I, 1) < 0 Then rnData(I, 1).EntireRow.Hidden = True
    If vaData(I, 1) < 1 Then rnData(I, 1).EntireRow.Hidden = True

    Next I
    End Sub

    Again thanks to everyone who responded, great to see so many varying methods and ideas.




    Thanks, tried the new code and it works, only thing is it's not much quicker.

    Loop = 51.97 seconds.
    No Loop = 51.71 seconds.

    Thanks anyway, always interesting to consider alternatives.


    Back again, had a look at some of the alternatives offered. The Autofilter is not suitable as it seems to work on predefined views and values, unfortunately my views and values will vary with each new worksheet.

    I tried your solution XLDennis but it keeps bugging out on me here:

    If vaData(i, 1) < 0 Then

    Sub Hide_Rows_0()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range
    Dim vaData As Variant
    Dim i As Long

    Set wbBook = ActiveWorkbook 'ThisWorkbook
    Set wsSheet = wbBook.ActiveSheet '.Worksheets(1)

    With wsSheet
    Set rnData = .Range("D1:D255")
    End With

    vaData = rnData.value

    For i = LBound(vaData) To UBound(vaData)
    If vaData(i, 1) < 0 Then rnData(i, 1).EntireRow.Hidden = True
    Next i
    End Sub

    I also tried to use your solution Kieran but it debugged out here:

    If ActiveSheet.[d1].Offset(I, 0).value <= 0 Then

    I'll have another look later when I get more time to see if I can tweek either to work.



    Thanks XLDennis, Richie, DaveH and Kieran for you EXCELent alternatives, appreciate the help. Am somewhat kicking myself though for not having thought through the alternatives.



    Hi All,

    I was hoping someone might have a quicker way of looping through a range?

    I use the following to loop through the cell value in D1:D251. If the value in the cell is greater than zero it does nothing, if less it hides the row and moves on to the next.

    Only problem is that it take yonks to loop through.......aggghhh people are way to impatient to wait 50 or 60 seconds.


    Selection.EntireRow.Hidden = False
    Application.ScreenUpdating = True
    Do While Counter < 251
    Counter = Counter + 1
    If ActiveCell.value &gt; 0 Then
    ActiveCell.Offset(1, 0).Activate
    Selection.EntireRow.Hidden = True
    ActiveCell.Offset(1, 0).Activate
    End If

    BTW: XL97 WIN98.




    Originally posted by DrGuru
    Is there a way, however, to trick Excel into thinking that there is at least one sheet visible?


    Yes, but it is not to trick Excel it is to use what Excel already allows. As you may be aware the normal extension for an Excel file is *.xls which is the standard. You can however also have a number of others but the type we need to aquaint you with is *.xla which is an addin file.

    An addin is essentially an *.xls workbook that has been created to carry out a specific function or functions, and is loaded into excel but is not visible, it normally is saved as with the *.xla extension but there is a little trick that allows you to have an *.xls addin.

    What we want is an *.xls file that has it's properties set to be an Addin rather than a woorbook, this way when it opens it is "invisible" until such time as the properties are changed. This achieves what you need, the next step is how to tell it to change the properties through the VBE (Visual Basic Editor), try playing with the following line of code and see if it fits with what you need to achieve.

    Application.ActiveWorkbook.IsAddin = True

    You can also set it to False

    Hope this helps.


    C:\temp\Addin Property.jpg


    Does the file you are trying to open contain a lot of links to other files ?

    If so you can save a lot of time loading the file if you use:"CFolderNameworkbook.xls") , UpdateLinks:=0

    This will stop the links from updating and load the file as it was last saved.

    Hope this helps.