Posts by Insomniac

    I dont think you can capture the entries in a sheet until enter is pressed. A possible solution may be to use a textbox to enter the digits and apply to the sheet with code.

    Using a textbox from the control toolbox and some code a bit like this:

    The Static variables will remeber what cell you are up to if you have to click back on the sheet to fix a mistake.
    You may need to enhance the code a bit to include a reset or backspace feature etc.

    This cumbersome single cell formula should work provided there is always 3 sets of brackets and you dont use the "~" tilde character in the strings:


    (Richie's UDF would be far more flexible though)

    You can set the variable as a required argument in the sub:

    Sub Macro1(mes As String) 'in BookA
        MsgBox mes
    End Sub
    Sub Macro2() 'in BookB
    Dim Str As String
        Str = "Hello"
        Application.Run "'BookA.xls'!Macro1", Str
    End Sub

    If you require the sub to return a value, change it to a function:

    Function Func1(a As Long, b As Long) As Long 'in BookA
        Func1 = a + b
    End Function
    Sub Macro3()  'in BookB
    Dim c As Long    
    c = Application.Run("'BookA.xls'!Func1", 5, 4)
        MsgBox c
    End Sub

    Pesky Weasel,

    Modified your code to work the same as mine, the slow part of my code was applying the array back to sheet2, your line:
    Sheets(2).Range("a1").Resize(iDiv, iElem).Value = data2 is a far better way.

    Hi there Jack, Had a bit of time this afternoon so thought I'd have a go:

    Hope this is something like what your after, see attached sample.

    (Think sample is too large too attache, made over 5000 rows of data but it works pretty quick)

    Yes, I knew my response was not exactly what you were after but I think it is an interesting challenge and am keen to see a more comprehensive answer and explanation from an array "Expert".


    Hi sleepless one

    hey yeah it is 4,30am here, bedtime a while away yet!

    Richie, combing your example2 with kundepuu's attempt I do not see any crashes.

    Note: I tried the CountA function on the array but it always returns the ubound of the array not the usual worksheet result.

    Just having a go!

    Iam not familiar with the technique you describe but it sounds like the OnTime method would solve your problem.

    By setting the Update to run OnTime, Excel will wait until edit mode is off before running the Update procedure.

    Thanks, Jack and Ivan for replies.

    I will mark this as solved if I can work out where to do it.

    I will stick with the addin as is, it works fine on most of the comps i have checked because of other buttons that i have added to the Worksheet Command Bar that pushes the Window button far enough to the right to make the dropdown appear under the control. On some comps it drops down to the right but it dosnt affect the the end result.

    At least I know I have not overlooked some simple property or setting!

    BTW been offline for a few days doing a complete reinstall as I turned on the comp and got "system.ini" error, could not recover from this error so into the cupboard to find all the recovery softwear etc. Computers can really be great fun sometimes especially when they crash for no apparent reason!

    Thanks again.

    p.s. Oh yeah there is a big green solved button, doh!

    hi again, iam still toying with this and would be interested in any further comments.

    Perhaps to recap, if you download the addin in my original post and set your screen resolution to 800x600 the dropdown list will align itself neatly to the bottom of the Window menu.If you set the screen resolution higher the dropdown list will appear to the right of the control.

    Can we force the dropdown to always align itself under or over the control no matter what resolution of the screen?

    Or am I trying to manipulate something that is not possible?

    p.s. the addin uses a class module to update the list whenever a workbook or sheet is added, and works as i wish, I
    want to avoid a completely seperate toolbar or other menu.

    Hi AndyPope.


    BTW, I assume you know that by right clicking the sheet selector buttons (bottom left of workbook) a list of worksheets is displayed. and if the list is too long a dialog is displayed

    yes iam aware of this method but my users know very little, besides that way will not display all the sheets in all the open workbooks.


    I believe the right align would be the normal behaviour.

    yes it seems that right align is the default behaviour, but i would prefer to align direclty below or above the control to avoid confusion with users who expect it to be so.


    How about creating the control on a toolbar. The default action there is to drop down below/above the control.

    yes this would suit myself but my users are very green and tend go to the Window button, it was therefore logical to add a control to to that menu to display all the worksheets available to them.

    At the end of the day Iam only trying to program around the lack of training and experience of my end users.

    OK Jack, thanks for the response. BTW never seen a post full of strikethrough before!.

    Anyway I tried your revised code but to no avail, I actually would prefer the dropdown list to appear either directly below or above the control itself regardless of screen resolurion.