vba if statement

[mafia]

hello, i've only ever used the record macro and then manually edit the code

but i've never used vba to do IF statements

what i am trying to do it

if cell A1 AND A2 = "YES"
after a 2 second delay
Cell A1 and A2 = "NO"

is that possible in vba?

and would i have to run the macro all the time if i want this to happen automatically?

thanks in advance :thumb

[Cosmo]

hello, i've only ever used the record macro and then manually edit the code

but i've never used vba to do IF statements

what i am trying to do it

if cell A1 AND A2 = "YES"
after a 2 second delay
Cell A1 and A2 = "NO"

is that possible in vba?

and would i have to run the macro all the time if i want this to happen automatically?

thanks in advance :thumb
Not completely sure I understand what you want to do.

If value of Cell A1= "YES" and value of Cell A2 = "YES", you want to set them both to "NO" after a 2 second delay?

[mafia]

yes that is what i mean, also in cell A3 i have the forumla '=now()' if that makes things any simpler?

do you think its possible?

[RonMcK]

Adding to Cosmo's questions:


... and would i have to run the macro all the time if i want this to happen automatically?
You would code what to do when this happens; VBA and Excel will handle watching for the triggering events/values and running your code.

You would need to set your code up as a worksheet_change event. It would then execute whenever A1 and A2 both become Yes.

Out of curiosity, why change the Yes/Yes to No/No after 2 seconds?


Puzzled,

[mdmackillop]

Also, is everything to be "frozen" during these 2 seconds or can further input occur?

[mafia]

Also, is everything to be "frozen" during these 2 seconds or can further input occur?

hhmmm, good point, i suppose, once it is A1=YES and A2=YES it instantly triggers the macro and even if suddenly changes to A1=YES and A2=NO it will still reset them both to NO after 2 seconds

can anyone help me by posting a sample code?:bow:

[david000]

Option Compare Text
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As String
Dim b As String

Set Target = Range("A1")

a = Target.Value
b = Target.Offset(1).Value

If a = "Yes" And b = "Yes" Then

Application.Wait (Now + TimeValue("00:00:02"))

Target = "No"
Target.Offset(1) = "No"

End If
End Sub

[mafia]

thanks david! i will test this tonight! i'll let you know how i get on

[mafia]

heres another quick one, i don't want to start a new thread for

i've been trying to do this with just formulas, but i can't seem to do it so i'm guessing it needs to be done in vba,


if callA1 goes over the value of 3 then+1 to cell A2, if A1 under 3 do nothing

thanks again

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value > 3 Then

Me.Range("A2").Value = Me.Range("A2").Value1
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[mafia]

thanks XLD very much!

[mafia]

.

[tpoynton]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[mafia]

this is regarding the first code, theres two questions on this thread

[mafia]

nevermind i've figured it out! thanks everyone