Posts by X10A

    Re: Workbook Password Protect 2 Levels With 1 Pwrd


    I believe that you cannot change the attribute of a file while it is opened. If I am not wrong, the VBA line "Workbook.ReadOnly" is used to test whether a workbook is set as Read-Only before it is opened.

    I suggested using the following:

    This code disables saving changes to the workbook, but it will make a copy of the workbook and prompts you to save under another name. I do not know how to disable Excel from automatically making a copy of the workbook though.

    Maybe another person will suggest a better way to do it?

    Re: Workbook_open

    Open up VBE and delete your code

    Private Sub workbook_open() 
    End Sub

    Now, still in VBE, select "Workbook" from the Code Window drop-down list. The default should be the Workbook Open event.
    Type "Sheet5.Select"
    Save and open up again.

    This is what I did with your sample file and I got it to work perfectly.

    Re: Worksheet Change Event To Track Changes On Seperate Sheet

    Hi, maybe you can try this.

    Re: History Logger

    Nice of you to share your code with us. It really promotes good spirit of giving and that makes people wanna give back too. Cheers!

    Re: Sheet Index In Vba

    It is quite difficult to visualise what you want to do exactly. You may want to try out the below revised code.

    Re: Sheet Index In Vba

    You have not included an "End IF" in your procedure. Please see my previous posting and substitute your code with my comments "...Code Goes Here". You should be able to get it working.

    Re: Sheet Index In Vba

    Hi, maybe you could try this

    Re: 12-hour Time Entry Stored As Text

    Hi Dave,

    Actually, I did not changed the settings. When I took over this PC, the previous user had left the Date Separator as nothing. Furthermore, Excel was having problem with Time not Date. So, when I check the Regional Settings, I looked at the Time tab and not the Date tab.

    It was only when I tried experimenting changing each value in the Regional Setting that I found that the Date Separator was the effect.


    Re: 12-hour Time Entry Stored As Text


    Well, I tried the following which all failed
    1) 3 pm
    2) 3:00 PM
    3) 3:00:00 PM

    One more thing I found is that when I entered 15:00 in the cell, Excel will pick it up as 3:00 PM. However, if I press F2 in the cell and Enter, Excel will once again treat it as text.

    Re: 12-hour Time Entry Stored As Text


    Thanks for your advice. I have tried all methods you given before, still Excel only accepts 24-hour data entry.

    I have also tried the Detect and Repair function under Help, it doesn't change a thing.



    I have a problem with entering 12-hour time data in excel.

    If I key in 3:00, Excel will recognise it as 3:00 AM.
    If I key in 3:00 p, Excel will recognise it as a text entry. To get Excel to recognise it as 3:00 PM, I have to use the 24-hour format. i.e. I have to type 15:00.

    I have checked the Regional and Language option in the Control Panel. Everything looks fine.

    Does anyone have such encounter with Excel before? Please kindly advise.

    Thanks & regards.

    Hi all,

    Let's say I have a date entry in cell A1 (the active cell).

    With VBA to evaluate if the active cell value is a text entry, I'll normally use the following which returns the correct results:

    If Application.WorksheetFunction.IsText(ActiveCell) Then
    '....Code goes here
    End If

    However, the following will give an incorrect evaulation

    If Application.WorksheetFunction.IsText(ActiveCell.Value) Then
    '....Code goes here
    End If

    But if I make a check in the immediate windows of VBE, the following will return True.

    ?ActiveCell = ActiveCell.Value

    I hope in posting my findings, others using the IsText function can have an idea where their code may be going wrong.


    Re: Determine If Entire Column Is Selected

    Hi Robert B,

    Thanks for your idea. However, I can't seem to get your code to work. I believe the problem lies in:

    R = Selection.End(xlDown).Row

    Let's assume I have some data in column A and it starts at the 2nd row. If I run your code, "R" will give me a value of 2 instead of the maximum rows in a sheet.

    Anyway, for anyone using these codes, I think Carlmack's code worked out fine. Nice.

    Hi, I have the following code which will determine if a user has selected a entire column.

    If Selection.Rows.Count = 65536 Then
    MsgBox "User has selected an entire column."
    End If

    Although it worked, but is there a better way to do it? I was thinking if future version of Excel goes beyond 65536 rows, then this code is definitely unusable!!

    Re: Unable To Retain Changes To Add-in Customised Toolbar

    Thanks to all for giving me suggestions and useful links. They are very helpful. However, I would like to avoid using VBA to create the toolbar. Instead, I just want the toolbar to be an attached toolbar to my add-in.

    This is what I have. My add-in have an attached customised toolbar called "Custom" and I have the following code in my add-in.

    Private Sub Workbook_AddinInstall()
    Application.CommandBars("Custom").Visible = True
    End Sub
    Private Sub Workbook_AddinUninstall()
    End Sub

    The above code will show "Custom" toolbar when my add-in is loaded. When I unload the add-in, the "Custom" toolbar will be removed.

    My problem is that if I add a new button to the "Custom" toolbar, the new button will be available as long as I do not unload the add-in.

    If I unload and reload the add-in again, the "Custom" toolbar will not have the new button in it.

    Thanks for looking into it!