Posts by cytop

    Re: Greater than today fun


    Use the WEEKDAY function to see if it's a Friday and change the amount you add to Now() accordingly or have a look in Help for the WORKDAY function but be aware you may need to load the Analysis toolpack.

    Re: Altering Date/Time format


    Changing the format won't change the underlying values, only how they appear on the sheet. If you want the absolute value '0722' to appear in the cell you're going to have to modify the data...

    Re: Excel matching problem


    Sorry Mike, this one has me stumped... Same sheet, no problem but over2 sheets it seems to be an issue. There are a few examples on the web about doing this using the Indirect function, but I cannot get them to work and I don't have the time to explore it any further...

    Not too sure what to suggest now short of coding some VBA... but perhaps there's a better brain than mine around here somewhere.

    Re: One of the 2 checkboxes have to be ticked


    Set one of them to have a default value of True at design time and then one will always be selected.
    It's a lot easier than adding check boxes and then coding to ensure one is selected.

    Coding to Windows design standards (which are much of a joke in any case) dicatate that option buttons are used where one item must be selected, and checkboxes for optional choices.
    J

    Re: Excel matching problem


    Use conditional formatting (Format/Conditional Formatting menu options - or at least in Word 2003 it is :))

    Assuming the first cell of the total list is A1 and the 'Completed list' is in B1:B350
    Select 'Formula is' for condition 1 and "=Countif($B$1:$B$350,A1)>0" for the formula, then use the format button to define whatever cell formatting is needed.
    Copy this cell and use Paste Special/Formats to set the conditional format for the other cells.

    Re: Preselected Listbox entries


    The snippet below will add 100 items to a listbox and select every 3rd item...

    Code
    Dim iTemp as Integer
     
       For iTemp = 0 to 99
     
          Listbox1.AddItem "Item " & cstr(iTemp)
     
          If iTemp \ 3 = iTemp / 3 then
             Listbox1.Selected(iTemp) = True
          Endif
       Next



    Don't forget to set the MultiSelect property of the listbox to something other than 0 (fmMultiSelectSingle)

    Re: Help with validating data entry via Inputbox... entry must NOT be on existing li


    Quck and dirty answer...

    There's plenty of sample code to strip characters from a string, have a quick Google for 'VBA replace characters'

    If you have a range called 'Checklist' then a simple search will work.

    Code
    Dim r as Range
    Set r = RANGE("Checklist").Find(What:=strInput) <- plus add any other required options for the search, see help...
     
    If not r is nothing then
       '// String was found
    Endif



    and likewise

    Code
    On Error Resume Next
    Dim w as worksheet
     
    Set w = Sheets(StrInput)
     
    if Err.Number = 0 then
       '// Worksheet exists...
    Endif



    ... but you MUST disable error trapping for when you try to address a non-existant worksheet

    Re: Macro tidy up


    You mean indents and things...?



    Other comments, and these are my personal preferences:

    Don't use in-line If...Else - Break them out to If...Else...End if

    You had a Dim x as Something, y as Something, c as Range in one line... one line per declaration is preferable

    All declarations are the top of the procedure

    It's usually not necessary to explicitly Select something before acting on it...
    Columns("M:R").Select
    Selection.EntireColumn.Hidden = True
    Could be written as
    Columns("M:R").Hidden = True - Not sure about that 'EntireColumnHidden', is that a property?

    If you don't already, set Application.ScreenUpdating = False when you start, and True when finished...

    I'm sure there's more, but someone else can try... :)

    J

    Re: Loop through userform controls based on backcolor


    How about...

    Code
    Dim t as Control
     
       For each t in me.controls
          if typeof t is msforms.textbox
             if t.backcolor = Something then
                '// Do Something
             endif
          endif
       Next



    The only way to reference a property of a control is either reference the control/property directly, or assign the control to a variable and then reference the property you want... and the For Each loop is the easiest way to do that.

    ?

    Re: Interesting Integer Overflow


    This might be a little late - but the problem is related to the way VBA and (VB, plus I believe .NET as well) handles numeric variables.

    For example:

    Code
    Public Sub Test
    [INDENT]Dim x as Integer
    Dim y as Integer
     
    x = 200
    y = 300
     
    MsgBox (x*y)   <- Throws an error
    [/INDENT]end sub



    The reason for the error is that VBA creates a 'shadow' variable with a precision equal to the highest declared variable. In this case, Integer. It then uses that variable to store the results of the calculation before passing it back to the call to Msgbox. However, the variable is an integer so it overflows.

    Even if you declare 'Z as Long' and try to assign Z = (X*Y) to it, the error will occur as VBA still creates an Integer shadow.

    The only way around this is to declare one of the input variables as Long, then the shadow will be a long.

    The same is happening here. AS Fencliff pointed out, the Case statements are assumed to have the same type as the Case comparison value (And 2 is an Integer) so it overflows when rows get to be greater than 32767 (or whatever...)

    This problem has existed in Visual Basic since version 1. 0 and for all I know in the DOS QuickBasic as well...

    J