The file is a timesheet. I am trying to add the functionality of sending the
entries to an Access database. I am new to VBA and therefore I am taking it
in baby-steps. All was going well until I try to simply add the string
"Edit" to a cell. I have tried this several different ways and all end up
going into a fatal loop (screen flashes and then crash). Everything else in
this bit of code works beautifully.

Here is what I am attempting to accomplish:
1. Check the current row to ensure that the "hours" column is greater than
zero.
2. Check the current row to ensure that the "project number" column is
populated.
3. If so, Lock and change color of cells "E" thru "I" of current row.
4. Then unlock and loose color of cell "J" of current row.
5. Add the word "Edit" to the same cell in step #4.

Here is the code...

For Each n In Range("Hours2")
If n.Value 0 And IsEmpty(Range("F" & n.Row).Value) = False Then
Range("E" & n.Row & ":" & "I" & n.Row).Locked = True
Range("E" & n.Row & ":" & "I" & n.Row).Interior.Color = RGB(219,
229, 241)
Range("J" & n.Row).Locked = False
Range("J" & n.Row).Interior.Color = xlNone
Range("J" & n.Row).Value = "Edit"
End If
Next n

The "Edit" cell is intended to act as a button to later unlock and edit
info after it has been added to the db, but I haven't gotten that far. In
case you are wondering I chose not to use actual buttons because each sheet
of this spreadsheet has about 100 rows each with the potential of it's own
button.

Please let me know what I am doing wrong here.

Thank you,
Jeff

I don't see anything in your code that would cause a loop (except for the
For/Each line).

Maybe you have an event macro sitting there waiting for a change to any cell and
it's firing???

application.enableevents = false
Range("J" & n.Row).Value = "Edit"
application.enableevents = true

would be how I would test it.

(Save often until you fix the problem!)

Spongebob wrote:

The file is a timesheet. I am trying to add the functionality of sending the
entries to an Access database. I am new to VBA and therefore I am taking it
in baby-steps. All was going well until I try to simply add the string
"Edit" to a cell. I have tried this several different ways and all end up
going into a fatal loop (screen flashes and then crash). Everything else in
this bit of code works beautifully.

Here is what I am attempting to accomplish:
1. Check the current row to ensure that the "hours" column is greater than
zero.
2. Check the current row to ensure that the "project number" column is
populated.
3. If so, Lock and change color of cells "E" thru "I" of current row.
4. Then unlock and loose color of cell "J" of current row.
5. Add the word "Edit" to the same cell in step #4.

Here is the code...

For Each n In Range("Hours2")
If n.Value 0 And IsEmpty(Range("F" & n.Row).Value) = False Then
Range("E" & n.Row & ":" & "I" & n.Row).Locked = True
Range("E" & n.Row & ":" & "I" & n.Row).Interior.Color = RGB(219,
229, 241)
Range("J" & n.Row).Locked = False
Range("J" & n.Row).Interior.Color = xlNone
Range("J" & n.Row).Value = "Edit"
End If
Next n

The "Edit" cell is intended to act as a button to later unlock and edit
info after it has been added to the db, but I haven't gotten that far. In
case you are wondering I chose not to use actual buttons because each sheet
of this spreadsheet has about 100 rows each with the potential of it's own
button.

Please let me know what I am doing wrong here.

Thank you,
Jeff

--

Dave Peterson

That did it.

Thank you. I don't think I could look at that code any longer. I am happy
to be able to move on now.

Jeff


"Dave Peterson" wrote:

I don't see anything in your code that would cause a loop (except for the
For/Each line).

Maybe you have an event macro sitting there waiting for a change to any cell and
it's firing???

application.enableevents = false
Range("J" & n.Row).Value = "Edit"
application.enableevents = true

would be how I would test it.

(Save often until you fix the problem!)

Spongebob wrote:

The file is a timesheet. I am trying to add the functionality of sending the
entries to an Access database. I am new to VBA and therefore I am taking it
in baby-steps. All was going well until I try to simply add the string
"Edit" to a cell. I have tried this several different ways and all end up
going into a fatal loop (screen flashes and then crash). Everything else in
this bit of code works beautifully.

Here is what I am attempting to accomplish:
1. Check the current row to ensure that the "hours" column is greater than
zero.
2. Check the current row to ensure that the "project number" column is
populated.
3. If so, Lock and change color of cells "E" thru "I" of current row.
4. Then unlock and loose color of cell "J" of current row.
5. Add the word "Edit" to the same cell in step #4.

Here is the code...

For Each n In Range("Hours2")
If n.Value 0 And IsEmpty(Range("F" & n.Row).Value) = False Then
Range("E" & n.Row & ":" & "I" & n.Row).Locked = True
Range("E" & n.Row & ":" & "I" & n.Row).Interior.Color = RGB(219,
229, 241)
Range("J" & n.Row).Locked = False
Range("J" & n.Row).Interior.Color = xlNone
Range("J" & n.Row).Value = "Edit"
End If
Next n

The "Edit" cell is intended to act as a button to later unlock and edit
info after it has been added to the db, but I haven't gotten that far. In
case you are wondering I chose not to use actual buttons because each sheet
of this spreadsheet has about 100 rows each with the potential of it's own
button.

Please let me know what I am doing wrong here.

Thank you,
Jeff

--

Dave Peterson

Hi Jeff,

I agree with Dave, it isn't obvious where the fatal loop is coming from.

Can you give more information on the range Hours2? Does it only contain the
hours column? Your code will cycle through every cell in the range, but even
100 rows by 100 columns shouldn't take too long.

Can you see what is happening if you step through the code using F8?

Sean.
--
(please remember to click yes if replies you receive are helpful to you)


"Spongebob" wrote:

The file is a timesheet. I am trying to add the functionality of sending the
entries to an Access database. I am new to VBA and therefore I am taking it
in baby-steps. All was going well until I try to simply add the string
"Edit" to a cell. I have tried this several different ways and all end up
going into a fatal loop (screen flashes and then crash). Everything else in
this bit of code works beautifully.

Here is what I am attempting to accomplish:
1. Check the current row to ensure that the "hours" column is greater than
zero.
2. Check the current row to ensure that the "project number" column is
populated.
3. If so, Lock and change color of cells "E" thru "I" of current row.
4. Then unlock and loose color of cell "J" of current row.
5. Add the word "Edit" to the same cell in step #4.

Here is the code...

For Each n In Range("Hours2")
If n.Value 0 And IsEmpty(Range("F" & n.Row).Value) = False Then
Range("E" & n.Row & ":" & "I" & n.Row).Locked = True
Range("E" & n.Row & ":" & "I" & n.Row).Interior.Color = RGB(219,
229, 241)
Range("J" & n.Row).Locked = False
Range("J" & n.Row).Interior.Color = xlNone
Range("J" & n.Row).Value = "Edit"
End If
Next n

The "Edit" cell is intended to act as a button to later unlock and edit
info after it has been added to the db, but I haven't gotten that far. In
case you are wondering I chose not to use actual buttons because each sheet
of this spreadsheet has about 100 rows each with the potential of it's own
button.

Please let me know what I am doing wrong here.

Thank you,
Jeff