• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Then Using VBA

K

Kinghart

Member
Hello... What I'm Trying To Do is Say,

d5:d10 = numbers say 2 to 6

I want D5:D10 to change its contents to blank"" when A5:a10 = true

example.. if A7 = true ... D7 should be " ".... and when a7 = false D7 should be 0

I know i should use a macro but couldn't figure out how

Thanks in advance...
 
Hi Kinghart,


This macro will execute when there is a change in your sheet. You must place the macro in you sheet module. Adjust the range as necessary.

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro must be place in the worksheet code
'Macro executes when there is a change in the sheet in range [A5:A10]

'Verify that we are in the range and only one cell is selected
If Intersect(Target, [A5:A10]) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

'if value in column A is TRUE then blank in column D
If Target = True Then Target.Offset(0, 3) = ""
End Sub
[/pre]
 
Kinghart


Could you simply put this in D5

Code: 
=If(A5=TRUE,"",0)


or


=If(A5,"",0)


Copy either down to D10
 
no can't do that Mr.Hui... Actually Column D is an input column related to column A...

The idea is that i put numbers in column d cells... but when cells in column A is true... column D cells should be blank... and when A is false, D should be 0 and then i could put another number in D...
 
GCExcel it worked perfectly... I added


If Target = False Then Target.Offset(0, 3) = 0


to get what I wanted...


thanks guys
 
hello again guys... need some more help in this...

Now I want to put a check box in column b linking to cells in column A.... i want to change the values in column D when i check/uncheck the boxes... column A changes True/False when I check and uncheck the boxes, but column D doesn't change... but when I write True and False in column A, Column D changes, but not when I check the boxes... any ideas??
 
Hi Kinghart,


Controls' Linked Cells don't cause Worksheet Change events to fire.


Try this. Move your/GCExcel's cocde to a separate procedure, and call that procedure from the Worksheet Change Event. Then create Click events for each checkbox control, and have those controls also call that procedure with a reference to the linked cell.


If your checkbox controls are called Checkbox1, Checkbox2, Checkbox3, Checkbox4, Checkbox5, and Checkbox6, replace your existing code with the following code in your Worksheet code section. I also modified the routine to handle a whole range of cells in column A changing at once (i.e. copy/paste):

[pre]
Code: 
Private Sub A_Cell_Change(ByRef Target As Range)
Dim Cell As Range
'if value in column A is TRUE then blank in column D
For Each Cell In Target
Select Case True
Case VarType(Cell) <> vbBoolean
' Do nothing if not true/false
Case Cell = True
Cell.Offset(0, 3) = ""
Case Cell = False
Cell.Offset(0, 3) = 0
End Select
Next Cell
Set Cell = Nothing
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Macro must be place in the worksheet code
'Macro executes when there is a change in the sheet in range [A5:A10]
'Verify that we are in the range
If Not Intersect(Target, [A5:A10]) Is Nothing Then
A_Cell_Change Target
End If
End Sub

Private Sub CheckBox1_Click()
A_Cell_Change Range(CheckBox1.LinkedCell)
End Sub

Private Sub CheckBox2_Click()
A_Cell_Change Range(CheckBox2.LinkedCell)
End Sub

Private Sub CheckBox3_Click()
A_Cell_Change Range(CheckBox3.LinkedCell)
End Sub

Private Sub CheckBox4_Click()
A_Cell_Change Range(CheckBox4.LinkedCell)
End Sub

Private Sub CheckBox5_Click()
A_Cell_Change Range(CheckBox5.LinkedCell)
End Sub

Private Sub CheckBox6_Click()
A_Cell_Change Range(CheckBox6.LinkedCell)
End Sub
[/pre]
Asa
 
Hi Kinghart,


Controls' Linked Cells don't cause Worksheet Change events to fire.


Try this. Move your/GCExcel's code to a separate procedure, and call that procedure from the Worksheet Change Event. Then create Click events for each checkbox control, and have those controls also call that procedure with a reference to the linked cell.


If your checkbox controls are called Checkbox1, Checkbox2, Checkbox3, Checkbox4, Checkbox5, and Checkbox6, replace your existing code with the following code in your Worksheet code section. I also modified the routine to handle a whole range of cells in column A changing at once (i.e. copy/paste):

[pre]
Code: 
Private Sub A_Cell_Change(ByRef Target As Range)
Dim Cell As Range
'if value in column A is TRUE then blank in column D
For Each Cell In Target
Select Case True
Case VarType(Cell) <> vbBoolean
' Do nothing if not true/false
Case Cell = True
Cell.Offset(0, 3) = ""
Case Cell = False
Cell.Offset(0, 3) = 0
End Select
Next Cell
Set Cell = Nothing
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Macro must be place in the worksheet code
'Macro executes when there is a change in the sheet in range [A5:A10]
'Verify that we are in the range
If Not Intersect(Target, [A5:A10]) Is Nothing Then
A_Cell_Change Target
End If
End Sub

Private Sub CheckBox1_Click()
A_Cell_Change Range(CheckBox1.LinkedCell)
End Sub

Private Sub CheckBox2_Click()
A_Cell_Change Range(CheckBox2.LinkedCell)
End Sub

Private Sub CheckBox3_Click()
A_Cell_Change Range(CheckBox3.LinkedCell)
End Sub

Private Sub CheckBox4_Click()
A_Cell_Change Range(CheckBox4.LinkedCell)
End Sub

Private Sub CheckBox5_Click()
A_Cell_Change Range(CheckBox5.LinkedCell)
End Sub

Private Sub CheckBox6_Click()
A_Cell_Change Range(CheckBox6.LinkedCell)
End Sub
[/pre]
Asa
 
Hi Kinghart,


Unless you use ActiveX controls, I believe worksheet event will not trigger. You could use ActiveX controls but I prefer to use the following method :


Instead of using checkbox, you can use a double-click event associated with column B.

Something like this for example:

[pre]
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, [B5:B10]) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

If Target = "X" Then
Target = ""
Target.Offset(0, -1) = False
Else
Target = "X"
Target.Offset(0, -1) = True
End If
Cancel = True

End Sub
[/pre]

Double-clicking in column B will change the value in column A and the change of value in column A will trigger the other event and update the value in column D...
 
You must log in or register to reply here.
Back
Top