Posts by Insomniac

  • Cell's Value vs Text Property

    Re: Cell's Value vs Text Property


    Just to add to comments, also of note is the Value2 property, particularly when dealing with dates.


    eg enter a date in A1 02/05/05 format the cell as dddd/mmm/yyyy.


    Code
    Sub test()
MsgBox ".Text   " & [A1].Text & vbLf & ".Value  " & [A1].Value & vbLf & ".Value2 " & [A1].Value2
End Sub


    you will get:


    .Text Monday/May/2005 '(the text as displayed in cell)
    .Value 2/05/2005 '(depends on your windows reginal shortdate)
    .Value2 38474 '(the date serial number)

  • Worksheet_Change and the Delete Key

    Re: Worksheet_Change and the Delete Key


    Hello ares11x, I'm not real sure what you are trying to do, you check if the Target intersects your range but then loop through every cell in your ranges anyway. Heres a simplified code of what I think you want:

  • updating an add-in used by users

    Re: updating an add-in used by users


    I make addins for use on public drive at work, all I do is develop it on my own comp then make a copy of it readonly & copy it to the public drive (set it to read only in explorer properties). You should then be able to copy over the existing file.


    Users would then get the latest version next time they open it, but if you want to force an update for people who never log off you would need some OnTime code to compare the file/date time, last modified against itself at regular intervals. This is probably more trouble than its worth.
    Assuming that everyone reboots now & then I dont have any probs doing it this way.

  • Load File that is not an EXE

    Re: Load File that is not an EXE


    As Andy Pope says CreateObject() approach is probably what you are looking for, but as an alternative try this:


    Code
    Sub OpenAnything()
Dim url As String
    url = "C:\My Documents\test word.doc"
    On Error GoTo NBG
    Shell "rundll32.exe url.dll,FileProtocolHandler " & url
NBG: 'error
    If Err Then MsgBox "ERROR " & url, vbCritical, "ERROR"
    Err.Clear
End Sub


    see: http://ericphelps.com/batch/rundll/


    (just place the full path to the file you want to open in the url string, eg hyperlink, mp3, word doc, excel workbook, etc. my experience is that windows will prompt you if it dosnt recognise the file type as to what app you want to open it with.)

  • preventing double entry

    Re: preventing double entry


    gazzapoh, bit late replying back, but I had to go to my day job!


    Anyway, to try to explain a bit:


    1.

    Code
    Private Sub Worksheet_Calculate()


    The calculation event code is only needed for xl97, we could also add as the 1st line of code If Val(Application.Version) > 8 Then Exit Sub so it dosn't run for later versions.


    2. Using the Calculation event is riddled with traps especially since your workbook may re-calculate when not the active workbook so the line: If Not Me.Parent Is ActiveWorkbook Then Exit Sub avoids any problems here.


    3. Also you dont want the code to run if your sheet is not the active sheet so the next line : If Not ActiveSheet.Name = Me.Name Then Exit Sub handles this.


    4. Next we check if the active cell is in column B & if it is pass the activecell to the Target range argument of the Worksheet_Change event.


    5. The rest of the code in the Worksheet_Change event should work for all versions later than xl97, we just need the Calculation bit to force xl97 to play nicely!


    6.

    Code
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    6a)

    Code
    If Not Intersect(Target, Me.[B:B]) Is Nothing Then


    this line exits the code if we are not in column B


    6b)

    Code
    Application.EnableEvents = False


    this is needed as we are going to clear the Target cell we do not wont to trigure a recursive call to the Worksheet_Change event.


    6c)

    Code
    For Each c In Target 
            If c.Column = 2 And c.Value > "" Then


    we check each cell in the Target range as we dont know if the user has clicked the dropdown or typed in manually or copied a multiple cell range within our target. For each cell in column B that is not empty we then determine if it is a duplicate.


    6d)

    Code
    If WorksheetFunction.CountIf(Me.[B:B], c.Value) > 1 Then 
                    i = c.Interior.ColorIndex 
                    c.Interior.ColorIndex = 3 'red
                    c.Select 
                    MsgBox "duplicate detected", vbCritical, "ERROR" 
                    c.ClearContents: c.Interior.ColorIndex = i


    We use excels CountIf function to determine if the C is a duplicate, if it is we asign the colour of the cell to i & set the C cell to red & select it & dispaly the message.
    Once user closes the message we clear the cell & reset to the original colour


    6e)

    Code
    Application.EnableEvents = True


    After we have checked all the cells within the Target we re-enable events ready for next time.



    I hope this sheds some light on the basic theory for you, it is not easy to explain it breifly!

  • preventing double entry

    Re: preventing double entry


    Hello gazzapoh, I'm getting a bit rusty with these xl97 workarounds, but give this a go.
    It should cover if user types manually or uses the validation list, or copies & pastes within the range.
    (you will need a formula in any other cell on the worksheet referencing column B to force it to fire, eg:=COUNTA(B:B))

  • Automatically execute code after Workbook_BeforeSave

    Re: Automatically execute code after Workbook_BeforeSave


    Richie, yep that Ontime does seem to work alright.


    The Ontime way may be easier as you dont have to worry about if the Save is SaveasUI or not. Using the method entirely in the Before Save event you would have to check for this & then need more code to get the filename etc.

  • Automatically execute code after Workbook_BeforeSave

    Re: Automatically execute code after Workbook_BeforeSave


    Just another couple of comments.
    I think Richie forgot to mention that along with Application.EnableEvents = False you will also need to set Cancel = True.


    The EnableEvents to False prevents recursive calls & then still need Cancel to True to prevent the double save.


    Something like this:


    Also I think with the Ontime method you cant use Now on its own as it will fire before the save is done, ie you need a slight delay:

  • Data Validation List, Change Event Workaround

    Re: Data Validation List, Change Event Workaround


    If you hardcode the list it should work in xl97, ie:
    Source = Monday,Teusday,Wednesday,Thursday,Friday


    You could apply it with code:


    The only real drawback is the Worksheet_Change Event will fire twice if the user enters wrong data & or cancels retry in xl97.


    A work aroung wich I have used is not to use the Validation.ShowError, but instead validate with code & give a message, ie:


    Because I'm declaring DAYS As String and setting it to the Validation Formula1 this method can be used for cells with completly different validation criteria & still work. I'm also using the Target cells Validation.InputTitle as part of the error message.
    Note: by hardcoding the list we avoid the traditional Calculation Event workaround for xl97 & validation.

  • Filtered Rows

    A slightly different approach that should give you the 1st cell in any filtered range using Excel's own 'hidden' name of "_FilterDatabase".
    (Excels creates this name for its own use & deletes it if nothing filtered.
    You can make this name visible with:
    ActiveSheet.Names("_FilterDatabase").Visible = True)


    Code
    With Range("_FilterDatabase")
    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
       .SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
End With


    Change .Cells(1,1).Select for all the cells in row1 of filtered range:
    .Rows(1).Select
    or for the entire row:
    .Rows(1).EntireRow.Select

  • Verb method of OLEObject class failed - Excel97

    You will probably find that the TakeFocusOnClick Property of the CommandButton set to True is the root cause of the trouble.


    Try setting it to False, but in anycase this code should work:
    [vba]Private Sub CommandButton1_Click()
    ActiveCell.Activate
    ActiveSheet.OLEObjects("Object 1").Verb Verb:=xlPrimary
    End Sub[/vba]

  • Capturing a followhyperlink event in Excel 97

    Dave's SelectionChange solution is the way to go for xl97 & Hyperlinks.


    None of the events are fired until the Hyperlink is resolved in xl97 & as stated the WorkSheet_Activate event is not trigured at all.
    If you have many Hyperlinks you may prefer a more generic solution where you dont have to code for every Target Address. I normally hide the column to the left of my Target Cells & use code like this:(need to force a selection change ready for next time).[Code goes in WorkSheet Class Module]

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.EntireColumn.Hidden Then
        Target.Offset(0, 1).Select
        'run any other code
    End If
End Sub


    Another more complex way if you require to know where the Hyperlink was activated & the destination:(this time hide the column to the right of the cell containing the Hyperlink).[code goes in ThisWorkbook Class Module]


    The above code is designed to only run if 1 cell with a Hyperlink to a different sheet in the same workbook is activated. This time it changes the selection of the origin cell ready for next time & gives a msgbox of Origin & Target. Also this will do nothing if a Hyperlink is to another Workbook or Webpage, etc.
    You can then pass the Sh, Target & WS variables to other subs if required.

  • Range Fill Down Method

    Hi guys, sounds like the xl97 TakeFocusOnClick bug.


    Try setting the TakeFocusOnClick Property to False of your CommandButton.


    Or use Activecell.Activate as the 1st line in the code.