Posts by X10A

Re: Workbook Password Protect 2 Levels With 1 Pwrd

Hi,

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:

Private Sub Workbook_Open() 
     
    Dim Message, Title, Default, Password As String 
    Dim wbname As String
    Message = "Enter your password" ' Set prompt.
    Title = "Password" ' Set title.
    Default = " " ' Set default.
     ' Display message, title, and default value.
    Password = InputBox(Message, Title, Default) 
     
    If Password = "test" Then 
        wbname = ThisWorkbook.FullName
        SetAttr wbname, vbReadOnly
    Else 
        wbname = ThisWorkbook.FullName
        SetAttr wbname, vbNormal
    End If
End Sub

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: Differences Between A Function And Sub Procedure

Yah, I agree with Reafidy. This really seems like a homework question.

Re: Workbook_open

Hi,
Open up VBE and delete your code

Private Sub workbook_open() 
    Sheet5.Select 
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: Summing Multiple Columns Multiple Times

Hi, how about using [pt]*[/pt] instead?

Re: Worksheet Change Event To Track Changes On Seperate Sheet

Hi, maybe you can try this.

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim SelRng As Range 
On error Goto ErrHandler
Application.EnableEvents = False     
    If Not Intersect(Target, Range("A3:K30")) Is Nothing Then 
        Set SelRng = Target 
        Application.ScreenUpdating = False 
        For Each cell In SelRng 
            Range(Range("A" & (cell.Row)), Cells(cell.Row, Columns.Count).End(xlToLeft).Offset(0, -5)).Copy 
            Sheets("Tracking").Select 
            ActiveSheet.Cells(Rows.Count, 31).End(xlUp).Offset(1, -13).Select 
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
            :=False, Transpose:=False 
            ActiveCell.Offset(0, 13).Value = ActiveWorkbook.UserStatus 
            Sheets("Engine 1").Select 
        Next cell 
        Application.CutCopyMode = False 
        Application.ScreenUpdating = True 
    End If 
ErrHandler:
Application.EnableEvents = True     
End Sub

Re: History Logger

Hi,Justin
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

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

Sub CopyDownFormulaeAllWorksheets() 
    Dim rCell As Range 
    Dim lStop As Long 
    Dim wSheet As Worksheet 
     
    For Each wSheet In Worksheets 
        If wSheet.Index > 8 Then 
            With wSheet 
                lStop = .Cells(Rows.Count, 12).End(xlUp).Row 
                For Each rCell In .Range("N10:U10").SpecialCells(xlCellTypeFormulas).Cells 
                    .Range(rCell, Cells(lStop, rCell.Column)).FillDown 
                Next rCell 
            End With 
        End If 
    Next wSheet 
End Sub

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

Sub CopyDownFormulaeAllWorksheets()
    Dim rCell As Range
    Dim lStop As Long
    Dim wSheet As Worksheet


    For Each wSheet In Worksheets
        If wSheet.Index > 10 Then
            With wSheet
                '...Code Goes Here
            End With
        End If
    Next wSheet
End Sub

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.

Regards.

Re: 12-hour Time Entry Stored As Text

Hi,

I have just found the solution. Please see the red circle in the attachment. In the Regional settings, I change the Date Separator to "-". I restart Excel and it recognise 12-hour time data now.

Thanks for all who have spend their time with me.

Regards.

Re: 12-hour Time Entry Stored As Text

Hi Dave,

Thanks for looking into it. Guess I have no choice but to live with it.

Regards.

Re: 12-hour Time Entry Stored As Text

Hi,

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

Hi,

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.

Rgds.

Hi,

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.

Thanks!

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, Dave! It did exactly what I needed. Such a simple step yet I would never have thought of that even if I cracked my head.

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()
Application.CommandBars("Custom").Delete
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!