only1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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:
 
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
 
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:
 
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:
 
My MAIN sheet got some coding.. will it crash?
The code should not be inside Data sheet right?

It cant works. Any advise?
 
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...
 
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?
 
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:
 
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!
 
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:
 
Thanks Stefi, i go check again.
Sry Toppers, my mail server is down. Have to check later.
Thanks both!
 
Back
Top