Posts by Quoth

    Re: Change cell colour when clicked


    Pockets,


    The following code should stop the colour change outside of the designated area.


    Why don't you want to sequential colour change? What would you prefer?



    Regards,


    Q

    Re: resume if error


    Hi,


    I find a handy way of bypassing a line of code should it error is as follows:


    Code
    on error resume next 'go to the next line if there is an error
    'Insert your line/lines of code here
    on error goto 0 'display errors should they occur


    The error, if ignored, can always be compounded further down the line!


    Hope this is helpful.


    Regards,


    Q

    Re: Replacing sets of names- there must be any easy way


    Hi,


    This bit of code replaces text with the first complete word from the text. Hope it does what you want - may need some more error trapping in place.



    Regards,


    Q

    Re: Change cell colour when clicked


    Poor colour selection in previous code!


    This is a bit better...


    Highlighting a range seems to work OK but if you want to change that range to the next colour you need to select another cell and highlight the exact same range.


    Re: Change cell colour when clicked


    Hi,


    I've changed to code to allow a colour change/cycle but it is far from ideal:


    It does tend to get into a muddle when you cover more than a single cell (which I would imagine would be the case with a timesheet).


    I'm wondering whether you may be better off with a keyboard shortcut that runs a macro which changes the colour based on current colour?


    Any other ideas on this?


    Regards,


    Q

    Re: Change cell colour when clicked


    Hi,


    The following code pasted into the sheet object will affect a colour change on selection change, i.e. clicking a cell within the range. Use the change event if you only want the colour to change when a value is changed.


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    
    If Not Application.Intersect(Range("A10:D30"), Target) Is Nothing Then
        Target.Interior.ColorIndex = 4
    End If
    
    
    End Sub


    Hope this helps.


    Regards,


    Q

    Re: Dynamic formulas


    Hi,


    If the list of serial numbers in column A is continuous something like this may work (assuming the values in column A start in row 2):


    =COUNTA(INDIRECT("B2:B" & COUNTA(A2:A65536)+1))


    Regards,


    Q

    Re: Macro vs. Add-in


    Hi,


    What I've done before is created the code in a workbook. Then I save as and .XLA (Excel Addin - the option is available on the 'save as' drop down).


    Then I go to 'tools - addins' and browse for the addin.


    That should make the code available to all workbooks.


    Regards,


    Q

    Re: Format dates & Dates operations


    Should never admit that!


    Official line should be along the lines of "...a mere glance of the proposed formula prompted the logical hemisphere of my brain to suggest that it was fundamentally flawed..."


    Q

    Re: Format dates & Dates operations


    Hi,


    This may work if you want it for display purposes only:


    (greater date in A1, lesser date in A2)


    =DATEDIF(A2,A1,"d")&" days and "&TEXT(A1-A2,"hh:mm")&" hours/minutes"


    Regards,


    Q

    Re: Userform, labels equals range in workbok, easier code


    Hi Obelix,


    Here is quick solution I drew up - no doubt someone will have a more elegant solution but this should do the job.


    (This was based on a sheet with two rows and two columns and assumes that all the labels have already been correctly named on the form)



    Regards,


    Q

    Re: Using application events with an AddIn


    Hi Andy,


    I've actioned the changes (my mistake...doh!) and the addin seems to load fine. However, the event doesn't appear to trigger when I open any workbooks.


    Any ideas?


    Regards,


    Q

    Re: Using application events with an AddIn


    Hi Andy,


    Thanks for your reply.


    I've implemented your changes but the code falls over when I load it. The error is with the following code in the standard code module:

    Code
    Public g_clsAppEvent As EventClassModule


    The error message is "Compile error: User-defined type not defined"


    Regards,


    Q

    Re: Macro vs. Add-in


    Hi Jim,


    Addins are Macros but they are available to all open workbooks. So if you want to write a specific function or procedure that you want to have available regardless of what workbook you have open addins are the way forward.


    For example, I have an procedure for entering a tick in a cell and changing it to a cross (it has an associated keyboard shortcut). I want this to be available to all spreadsheets I work in rather than the one I created it in and therefore saved it as an addin.


    Coding wise there aren't many differences. I suppose the one thing to be wary of is that you don't make a reference to a specifically named sheet/object.


    Hope this was helpful.


    Regards,


    Q

    Hi All,


    I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).


    I have created a class module with the following code (exactly as the Excel help):


    The help says "After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."


    When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".


    I realise I have probably omitted some funamental step somewhere but your help would be most appreciated!


    Regards,


    Q