Posts by FWind


    ...or you can use the following code in a worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If IsEmpty(.Value) Then
                If .Comment Is Nothing Then .AddComment
                .Comment.Text Text:=CStr(.Value)
            End If
        End With
    End Sub


    This code finds all the occurances of the sought after value and writes the cell address of each cell in a separate column



    Here's a macro that may help you. It assumes you have a worksheet named "ListOfValues", but you can change that in the code if you like.



    One solution is to add a custom list of a,b,c,...

    Just type the list you want in a range of cell and select Tools | Options | Custom Lists.
    Select the range in Import list from cells and click Add.

    Another is to use the following formula in cell A1




    The problem is in the second MATCH-functon:


    If you add a semi colon and use a third argument


    the function will return the correct column



    The following code makes a copy of the active sheet as a picture and places it on the clipboard:

    ActiveSheet.Cells.CopyPicture Appearance:=xlScreen, Format:=xlPicture

    You can check out the CopyPicture method in the Help for more info.

    Hope this helps

    Hi all.

    I thought I should share some info that may not be that well known in the VBA-world - conditional compiling.

    First, I apologise that the post is a bit long...

    Users of C and C++ can define constants that defines how the program code shall compile. This feature exists in VBA too. :bouncing:

    Let me give you an example right away:

    By using conditional compiling we can define what parts of the program should by compiled and run.

    During compiling, the compiler only compiles code that satisfies the #If...Then...#ElseIf...#End tests.
    In the above case, the actual compiled code reads:

    So, why use conditional compiling?
    Well it opens up a variety of possibilities:

    • Adding and removing debug and trace code by setting a single parameter
    • Handle the small (annoying) differences between Word, Excel, Access and Powerpoint (e.g DDERequest returns an array in Excel and a tab-separated string in Access)
    • Switching between production and test evironment with a single parameter

    I hope this can be of use for someone (at least it increases my post count :wink1: )

    Hi xl_spec

    The following code will prevent the creation of any new windows:

    Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
    Do Until ThisWorkbook.Windows(1).Caption = ThisWorkbook.Name
    End Sub

    Place it in the ThisWorkbook module and give it a try.

    Hope this helps...


    ActiveWorkbook.SaveAs Filename:=Range("B1").Value

    (Range("B1").Select selects cell B1 and returns the result; TRUE if successful, FALSE otherwise)

    Actually you don't need to type Value as this is the default property of the range object.


    You can use the COUNTIF function to check how many times a certain value appears in a range. You can then use "Conditional Formatting" to display the duplicates. See the example workbook.