only1

K

kyoshirou

how to make user to fill in at most value of 1 inside
cell c3,c4,c5.
This means if c3 is inserted 1, c4 n c5 will auto set to 0.
Or if c4 is inserted 1, c3 n c5 will set to 0.
Or if c5 is inserted 1, c3 n c4 will set to 0.

thank
 
S

Stefi

You have to use a Worksheet_Change event sub:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Application.EnableEvents = False
Select Case Target.Row
Case 3
Range(Cells(4, Target.Column), Cells(5,
Target.Column)).Value = 0
Case 4
Cells(3, Target.Column).Value = 0
Cells(5, Target.Column).Value = 0
Case 5
Range(Cells(3, Target.Column), Cells(4,
Target.Column)).Value = 0

End Select
Application.EnableEvents = True
End If
End Sub


Apply Data validation on cells c3:c5 to limit the value to 1.

Regards,
Stefi

„kyoshirou†ezt írta:
 
K

kyoshirou

Hi Stefi,

i trying to do this:
user can only enter one of the cells of C3,C4 or C5.
And the value can only be 1. Other 2 cell i have auto set to 0 value.
I mean user can enter:

c3: 1
c4: 0
c5: 0

or
c3: 0
c4: 0
c5: 1

or
c3: 0
c4: 1
c5: 0
 
S

Stefi

Hi Kyoshirou,

The sub I attached does exactly what you need: if a value is entered in any
of cells C3,C4,C5, the other two will be automatically set to 0. Applying
Data validation to these cells will allow entering by typing only value 1 to
any of these cells.

Post if you need further assistance for installing the event sub!

Regards,
Stefi


„kyoshirou†ezt írta:
 
S

Stefi

Invoke VBA (Alt+F11)
In Project Explorer window (Ctrl+R) right-click on the sheet name containing
your C3:C5 cells, choose View Code from the local menu
Choose Worksheet from the Objects drop-down list (on left hand side)
Copy the attached sub into the window below

Stefi

„kyoshirou†ezt írta:
 
K

kyoshirou

My MAIN sheet got some coding.. will it crash?
The code should not be inside Data sheet right?

It cant works. Any advise?
 
T

Toppers

See e-mail I sent you!!!

kyoshirou said:
My MAIN sheet got some coding.. will it crash?
The code should not be inside Data sheet right?

It cant works. Any advise?
Click to expand...
 
K

kyoshirou

Oh.. i know already. THanks!
But then now the default is 0.
Can i set it to empty? Only when users start to touch the cells, then it
will become 0?
 
S

Stefi

When do you want to set the value of these cells to default 0? When opening
the workbook? If so, you have to apply a Workbook_Open event sub, like:

Private Sub Workbook_Open()
Range("C3:C5").Value = 0
End Sub

Invoke VBA (Alt+F11)
In Project Explorer window (Ctrl+R) right-click on the workbook name, choose
View Code from the local menu
Choose Workbook from the Objects drop-down list (on left hand side)
Copy the attached sub into the window below

Regards,
Stefi


„kyoshirou†ezt írta:
 
K

kyoshirou

Hi Stefi,

I would like the cells to appear everthing empty, only after the user start
to enter one of the 3 cells. Then, the cells will become to 0. (When it is
not 1).

Thanks again!
 
S

Stefi

Kyoshirou,

I do not really understand your problem. The sub does exactly what you
wrote. The cells are entirely empty as default until the user enters a value
in one of them. Then the value of the other two cells changes to 0. Please
clarify your request, if you need something else.

Stefi


„kyoshirou†ezt írta:
 
K

kyoshirou

Thanks Stefi, i go check again.
Sry Toppers, my mail server is down. Have to check later.
Thanks both!
 
Top