Hi,

I am looking for a vb code to do the following.

1. Unprotect sheet
2. Enter X in the active cell
3. Show a dialogue box before entering X which says "Are you sure you want
to mark X?" with the options Yes - No
4. If the user presses Yes enter X.
5. Protect Sheet.

Now there are few other things to it. If we already have one X in that
column, the column should turn grey and should be locked.
If we have 2 Xs already in that column then a dialogue box should appear
saying "This is the third X in this column, are you sure you want to do
this?" The moment the user presses ok the column turns red.

The dialogue boxes are optional..i mean its good if i have them.

Thanks for any help.

If MsgBox("Are you sure you want to mark X?",vbYesNo) = vbYes Then

With Activesheet

.Unprotect
Activecell.Value = "X"
.Protect
End With
End If

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Gaurav" wrote in message
...
Hi,

I am looking for a vb code to do the following.

1. Unprotect sheet
2. Enter X in the active cell
3. Show a dialogue box before entering X which says "Are you sure you want
to mark X?" with the options Yes - No
4. If the user presses Yes enter X.
5. Protect Sheet.

Now there are few other things to it. If we already have one X in that
column, the column should turn grey and should be locked.
If we have 2 Xs already in that column then a dialogue box should appear
saying "This is the third X in this column, are you sure you want to do
this?" The moment the user presses ok the column turns red.

The dialogue boxes are optional..i mean its good if i have them.

Thanks for any help.

Sub AddX()
Dim n As Long, ret
ActiveSheet.Unprotect
n =
Application.WorksheetFunction.CountIf(ActiveSheet. Columns(ActiveCell.Column),
"X")
Select Case n
Case 0
ret = MsgBox("Are you sure you want to mark X?", _
vbYesNo, "Please confirm")
If ret = vbYes Then ActiveCell.Value = "X"
Case 1
ret = MsgBox("Are you sure you want to mark X?", _
vbYesNo, "Please confirm")
If ret = vbYes Then
ActiveCell.Value = "X"
ActiveCell.EntireColumn.Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Locked = True
End If
Case 2
ret = MsgBox("This is the third X in this column, are you sure you
want to do this?", _
vbYesNo, "Please confirm")
If ret = vbYes Then
ActiveCell.Value = "X"
ActiveCell.EntireColumn.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Case Else
'do something
End Select
ActiveSheet.Protect
End Sub

Hope this helps,

Hutch

"Gaurav" wrote:

Hi,

I am looking for a vb code to do the following.

1. Unprotect sheet
2. Enter X in the active cell
3. Show a dialogue box before entering X which says "Are you sure you want
to mark X?" with the options Yes - No
4. If the user presses Yes enter X.
5. Protect Sheet.

Now there are few other things to it. If we already have one X in that
column, the column should turn grey and should be locked.
If we have 2 Xs already in that column then a dialogue box should appear
saying "This is the third X in this column, are you sure you want to do
this?" The moment the user presses ok the column turns red.

The dialogue boxes are optional..i mean its good if i have them.

Thanks for any help.