Replies

Thanks everyone. Has anyone used the XP FAX support who has also used Symantec’s WinFax Pro. I have a copy of that which I used on Win 2K and I really liked how it worked. If so, can you compare how the two work. I don’t think that WinFax Pro is still available, but the box says it works on XP so I am tempted to try installing it.

Because the prorammers spent all their time working on the *^$# Registration/Activation Wizard?

Hey!!!!!! I am the one who is getting to that age.

I do not understand. If the .End(XLUP) stops at a non-empty cell then are you saying that IsEmpty(LastCell) will be true?

Yup, that is what I said in my last message. LastCell is set to the last cell with a value in it, therefore the IF IsEmpty(LastCell) is NEVER true. Try:


Set LastCell = .Cells(.Rows.Count, "J").End(xlUp)


to set LastCell to the cell below the last cell that is not empty.

In addition to the missing End If that Hans pointed out, I see two additional things.

1- The line that reads:


Set LastCell = .Cells(.Rows.Count, "J").End(xlUp)


finds the last non empty cell in column J. Therefore, the following IF statement will never see an empty cell except when there is nothing in column J.

2- The line which reads:


.Cells(Irow, 1).Value = Me.txtEmployee


uses variable lrow to determine where to store the value. However, lrow is never assigned a value and will therefore always be the default zero. This will result in a run time error since zero is an invalid subscript for Cells.

Here is possible solution. First, unlink the textbox from the cell on the worksheet. Then remove your textbox change event code and put the code below in the module behind the userform:


Private Sub TxtCost_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim curCost As Currency
If TxtCost = vbNullString Then Exit Sub
If Not IsNumeric(TxtCost) Then
MsgBox "Sorry, numbers only"
TextCost = vbNullString
Else
curCost = TxtCost
Worksheets("Sheet1").Range("A1").Value = curCost
End If

End Sub


Change “Sheet1” and “A1” in the above code to the sheet and cell where the cost is to be stored. Format the cell to display the value as you desire.

It would make a lot more sense to me to use:

LogDate = Replace(Date,”/”,”-“)

That should make it look like this:

Status02-02-2008.log

Which is a lot easier to read and know what it is.

As written, the code is confined to having cells in the range C4:C13 selected. However, the code assumes that only one cell will be selected, and that no cells outside of that range will be included in the selection. If the cells in the range C4:C13 can be selected by clicking in them, then they probably can be selected by tabbing into them. In other words, I think there are a number of possible problems with the code as written. You will have to more precisely specify how you want this to work (see my other reply) before code can be written without these problems.

No, using On Error will not “Handle this”, it will just hide the problem. Using the Worksheet BeforeDoubleClick event will prevent the problem from happening. If you really do want to use the Worksheet SelectionChange event, then you will need to define what you want to happen if the user selects more than one cell in the given range. Do you want all of the selected cells to get the “x”? None of the cells? One particular cell, if so which one? Something else.

That code will put a “x” into the cell if the active cell is a different cell and the user clicks in the cell. However, if the active cell is is already the cell (lets say the user selected the cell and then deleted the “x”), no “x” will be put into the cell. Also, if the user selects the cell by other means (tabs into it or gets there after pressing return, or by the arrow keys for example), then the “x” will be put into the cell. I think this would be a little too unpredictable for most users. Wouldn’t it be better to use the BeforeDoubleClick event? I would also recommend disabeling events before putting the “x” into the cell and then enabeling them again after. Putting the “x” into the cell will trigger the worksheet change event and possibly the worksheet calculate event which could result in a cascading event loop. If you do that, then you would also need to force a recalculate after reenableing events if there are formulas that are dependent on the cell where the “x” is being placed.

My experience is that shared workbook become corrupted very frequently. If you must share a workbook, I would recommend backing it up every couple of hours and keep ALL of the backups so you can work back until you find one that is not corrupted.

This would be easy to do if you wanted the comment in a cell rather than in a comment. If you want it in a cell, then you could use a formula like:

=”SPMH ” & TEXT(C1/D1,”$###,##0.00″)

If you must have it in a comment, then you will need to use VBA code in the Worksheet_Change event routine.

It is still plugged in. I have just recently installed two new USB devices with no problems.

Try putting this modification to Hans code in the Thisworkbook module.


Private Sub Workbook_Open()
Dim r As Long
Application.EnableEvents = False
r = Worksheets("UsageLog").Cells(Rows.Count, 2).End(xlUp).Row + 1
Worksheets("UsageLog").Range("B" & r) = Now
Worksheets("UsageLog").Range("A" & r) = Environ("username")
Application.EnableEvents = True
End Sub