Posts by Jong

    Here's how you could do it:

    I would think that there would be a function to acheive this. If you cannot find one, then you can code your own.

    Lets say this range is a list of unsorted values:


    The resulting list will be put in Worksheets("Two").Range("A1:A20")

    I just wrote the above code from memory, it is untested. This should give you a pretty good idea as to what you will need to do what is needed to do. The above code will only work if your list is all located in the same place. It will sort out the duplicates.

    Hope it helps.


    there is an option when you protect the sheet, to allow the user to "Format Columns"

    This will not allow the user to insert data in the cells, but will allow him to resize columns, and hide/unhide columns.

    To achieve this, you can click on Tools > Protection > Protect Sheet
    Then select "Format Columns"

    Alternatively, this can be done programatically with the following code:

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True

    The easiest way to do this is to create a Public variable. I suggest creating this variable in the location where you are trying to "pass" it to.

    For example, lets say this is the Module you wish to pass the string to, and this module is called "Module1":

    Module1 Code:

    'This should be put anywheres in the first lines of code in the module
    [CODE]Public MyVariable as String


    Userform code:

    'Wherever you exit the form, put this line
    Module1.MyVariable = "This is the string to pass"

    Something like this maybe?
    Paste this in the worksheet object where you would like the columns to be hidden. Specify the range you want to check. For my example, I used E1:E30

    Regular expressions are used like this:

    You will need to add a reference to "Microsoft VBScript Regular Expressions" in your VBA Editor > Tools > References.

    For more help on Regex patterns, check out

    Here is how i accomplish your requested task:
    NOTE: You may have to add a reference to Microsoft scripting Runtime in your VBA Editor (Tools > References)

    You may have to delete your menu at some point, to do so, here is the code:

    Function DeleteCommandBar()
        On Error Resume Next
            For Each cb In Application.CommandBars
            Next cb
        On Error GoTo 0
    End Function

    This is how i use open file dialogs:

    Dim s As String
        s = Application.GetOpenFilename("FILE TYPE (*.txt),*.txt", _
            1, "DESCRIPTIONS", , False)

    Of course there is a way!

    Here is some code that may point you in the right direction:

    Dim RowPtr as Integer
    RowPtr = 1
    While Worksheets("MySheet").Range("A" & RowPtr) <> ""
        Worksheets("MySheet").Range("B" & RowPtr) = "FILL"
        RowPtr = RowPtr + 1

    Piece of cake. Lets say the Function to create the menu bar is called "CreateCommandBar"... just add the following SUB anywhere in your Modules, and it will auto run.

    Sub Auto_Open()
        Call Utils.CreateCommandBar
    End Sub

    In your "ThisWorkbook" Object in your VBA Editor, add the following:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
       'put code here
    End Sub

    The code will execute before a save takes place. You can then just use whatever function you want to paste the current date/time in whatever location you want.