Edit Cell.

[Nigel Martin]

I need some code that will run from within a macro that will take a user to a specific cell in edit mode and will not let them leave the cell without entering a new piece of data. Any ideas?

[Juan Pablo González]

Do you want to leave the user in edit mode ? or begin the macro being in edit mode ?

First is doable, second not.

[Joe Was]

These two macros run from a hot-key or Form button, you may convert then to an event, so they will run automatically, if you want. Hope these help. JSW

Sub tCellDat()
'Test each of the required cells for data.
'The total(CountA(If cell has data)Union)=the total # of cells listed!
If WorksheetFunction.CountA(Union([B5], [D5], [F5:F7], [H5])) = 6 Then
'Everything ok
MsgBox "All required cells have data!"
Else
Cancel = True
MsgBox "There are some required cells missing, please fix and try again!"
End If

End Sub

Sub tICells()
'Test one cell or range for required data.
Dim myCell, MyCheck

'Indicate which Cell Range.
Set myCell = Worksheets("Sheet1").Range("B5")
'Select cell range for flag.
myCell.Select
'Test cell range for data.
MyCheck = IsEmpty(myCell)

'Direct test.
If MyCheck = True Then GoTo Flag
If MyCheck = False Then GoTo Good

Flag:
'Color cell range Lt.Blue if data is missing.
Selection.Interior.ColorIndex = 34
MsgBox "Cell {B5} is missing the required data!" & Chr(13) & Chr(13) & _
"Please fix this cell and try again!"
End

Good:
'Color cell range Lt.Yellow if data is present.
Selection.Interior.ColorIndex = 36
MsgBox "All required cells have data!"
End Sub


[ This Message was edited by: Joe Was on 2002-05-24 11:01 ]

[Nigel Martin]

Hello Juan Pablo G.

In answer to your question it is an answer to the first option I need.

Regards
Nigel.