Posts by lenze

Re: Formula: Input in anyone of three cells solves for other two.

So, you DON'T really need to place a formula in any of the cells. You just want to calculate and populate the other 2 when 1 of them is changed. Is that correct??. The code above does that, but we can't help you unless we know the formulas.

lenze

Re: Formula: Input in anyone of three cells solves for other two.

Post YOUR formulas, although I 'm not really sure of what you need.
lenze

Re: Formula: Input in anyone of three cells solves for other two.

Pivate Sub WorkSheet_Change(ByVal target as Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Address
    Case "$F$10"
        'Code to calculate G10 & H10
    Case "$G$10"
        'Code to calculate F10 & H10
    Case "$H$10"
        'Code to calculate F10 & G10
Case Else:
End Select

lenze

Re: Auto Sort

Use a WorkSheet Change Event on H4:H16 and place the sort code in it. You can get the syntax by recording a macro while preforming a Sort
lenze

Re: Automatically Add The Username And Date In Cells

Like this??

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row <> 3 Then Exit Sub
If UCase(Target) = "X" Then
Cells(4, Target.Column) = Date & " " & Time & " by " & Environ("UserName")
End If
End Sub

That said why not use a comment?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Row <> 3 Then Exit Sub
    If UCase(Target) = "X" Then
    Target.ClearComments
    Target.AddComment.Text Text:="Revised " & Format(Date, "mm-dd-yyyy") & " at " & Time & Chr(10) & "By " & Environ("UserName")
    End If
End Sub

lenze

Re: Log Worksheet Changes

Another approach that I find useful is to use a comment to track changes

Option Explicit 
Public preValue As Variant 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 
    Target.ClearComments 
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 
End Sub 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 
    If Target = "" Then 
        preValue = "a blank" 
    Else: preValue = Target.Value 
    End If 
End Sub

Reference http://vbaexpress.com/kb/getarticle.php?kb_id=909

lenze

Re: Counting Number Of Instances Of A Value

Just use a Pivot Table. Put Date in the Row filed, Zip in the row field and Zip in the in the Data Field. Make sure the Data field is set to Count

lenze

Re: Don't Overwrite Cells

Don't know if it will work with a PT, but try

Application.DisableAlerts = False

lenze

Re: Convert Decimal Hours To True Time

=DOLLARFR(A1,60) turns 1.5 into 1.30

=DOLLARDE(A1,60) turns 1.30 into 1.5
Analysis ToolPak required

This can be automated. See http://vbaexpress.com/kb/getarticle.php?kb_id=882

lenze

Re: Changing The Font Color Of Formulas

Perhaps

Private Sub Worksheet_Activate()
Cells.SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3
End Sub

lenze

Re: If And Nested Functions

You can shorten your formula to
=IF(OR(N10={1,2,3,4,5,}),0,G10)
lenze

Re: Select Adjacent Surrounding Cells

Aaron: I really like your nested approach. I just never thought of it.
I also thought he could use Cells

Cells(ActiveCell.Row-3,1).Resize(7,1).....

lenze

Re: Selecting Adjacent Cells

You may need to combine Offset with Resize

Range("C10").Offset(-3,0).Resize(7,1).Select

Are you sure you really need to select? You usually can work with ranges without selecting them.

Range("C10").Offset(-3,0).Resize(7,1).Copy Sheet2.Range("$A$1")

This will copy C7:C13 To Sheet2 A1.
Edit: Yu can use ActiveCell instead of a range such as C10

ActiveCell.Offset(-3,0).Resize(7,1).Copy Sheet2.Range("$A$1")

lenze

Re: Lock Cells Passed Current Date

Where did you place the code/ it should be in the ThisWorkBook module. Right click the Excel icon left of "File" on the menu bar and choose "View Code"

Also, if row 1 is a header row, change the line

For Each cl In Sheet1.Range("$A$1:$A" & Range("$A$65536").End(xlUp).Row)

to

For Each cl In Sheet1.Range("$A$[b]2[/b]:$A" & Range("$A$65536").End(xlUp).Row)

lenze

Re: Lock Cells By Current Date

Assuming Date in Column A

Private Sub Workbook_Open()
Dim cl As Range
For Each cl In Sheet1.Range("$A$1:$A" & Range("$A$65536").End(xlUp).Row)
If cl < Date - 1 Then Range(Cells(cl.Row, 1), Cells(cl.Row, 21)).Locked = True
Next cl
End Sub

lenze

Re: Changing Font Color Depending On Adjacent Cell Color

Sub ChangeFont()
Dim cl as Range
For Each cl in Range("$B$2:$B" & Range("$B$65536").End(xlUp).Row)
cl.Font.ColorIndex = cl.Offset(0,-1).Interior.ColorIndex
Next cl
End Sub

lenze

Re: Popup Message Before Saving With Conditions

Since your sample does not contain code, I'm guessing Dave used a BeforeSave Event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Range("D7") = "RET" And Sheet1.Range("D15") = 0 Then
    MsgBox "Whatever"
    Cancel = True
End If
End Sub

lenze

Re: Unprotect And Reprotect The Sheet After Update Cells

Is your copying done via Code or manually?

lenze

Re: Adding A Value To A Value

Something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Range("$E$1") = Range("$E$1") & " " & Target.Text
End Sub

Assumming your dropdown is in $C$1 and your cell to append is in $E$1

lenze

Re: Double Click In Cell To Show Current Date And Time

Perhaps

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("$C$2:$E$400")) Is Nothing Then Exit Sub
Target = Now
Cancel = True
End Sub

But this may be of interest
http://vbaexpress.com/kb/getarticle.php?kb_id=909