Using VBA for formulas in sheet

[coreyalaurence39]

I am having an issue with the attached sheet. I have equations in column D, F and K. I need assistance with VBA code to do the calculations so that there are not formulas in the cells. Is this possible to do with these three columns?

[TMS]

Try:

Please Login or Register  to view this content.

[coreyalaurence39]

Is there a way to do this without it being a macro. For example right now when the date is changed in B2 column D auto populates the date that is in B2. When the name in column G changes column F auto changes the code and when the times in column H & I are changes Column K total hours changes. Can this be done with VBA or does it have to be a macro that the user has to initiate when a change is made. I hope that makes sense.

[TMS]

A macro IS VBA. You can use a Worksheet Change Event handler to monitor column G and, when it changes, either drop the formulae in and convert them to values (effectively what the macro does) or evaluate the formulae internally.

If columns H and I are manually changed and column K depends on them, then they need to be monitored too.

[TMS]

Try:

Please Login or Register  to view this content.

[bebo021999]

Or try worksheet_Change even:

Option Explicit 
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim Lr&, f 
Lr = Cells(Rows.Count, "G").End(xlUp).Row 
    'If any value in B2, column G,H,I change then update 
    If Not Intersect(Target, Range("B2")) Is Nothing Or Not Intersect(Target, Range("G4:G" & Lr)) Is Nothing _ 
    Or Not Intersect(Target, Range("H4:H" & Lr)) Is Nothing Or Not Intersect(Target, Range("I4:I" & Lr)) Is Nothing Then 
        For Each cell In Range("D4:D" & Lr) 
            cell.Value = Range("B2").Value ' update column D with date in cell B2 
            cell.Offset(0, 7).Value = cell.Offset(0, 5).Value - cell.Offset(0, 4).Value ' update total hour 
            ' vlookup EE code update 
            With Sheets("Employee Master") 
                Set f = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Find(cell.Offset(0, 3).Value) ' loop thru name list 
                If f Is Nothing Then 
                    cell.Offset(0, 2).Value = "" ' if vlookup failed, return blank cell 
                Else 
                    cell.Offset(0, 2).Value = f.Offset(0, 1) ' return EE code 
                End If 
            End With 
        Next 
    End If 
End Sub  

[coreyalaurence39]

I would like to know if code could be provided that will open a vba user form when the file is opened that will fill in cell B1 with the name and B2 with the date. I would like the user to have to fill in a user form with data that will than input the data into these cells. I hope this makes sense.

[coreyalaurence39]

Can I also ask how I would make the user required to fill in the information before they can use the file?

[TMS]

I would like to know if code could be provided that will open a vba user form when the file is opened that will fill in cell B1 with the name and B2 with the date. I would like the user to have to fill in a user form with data that will than input the data into these cells. I hope this makes sense.

Can I also ask how I would make the user required to fill in the information before they can use the file?

Your question has been answered. Please mark it as solved. This is a new question and rider. Please start a new thread. Provide a link back to this thread for background.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.