Anyone Handy with VBA for Excel?

[The Boosh!]

If so would you mind PMing me your e-mail address so I can pick your brains?

In short i'm wanting a macro to activate as soon as a certain value is celected.

IE if cell a1 = 1 I want the code i've wrote to run.

Thanks

 

[Sym0n]

Too busy to help out, but try googling worksheet_change event. Should do what you're after or lead you on to something that will.

 

[The Boosh!]

You have gone up in my estimations symondo!

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a1") = "QVN" Then
    Rows("5:7").Select
    Selection.EntireRow.Hidden = True
    Range("A1").Select
Else
    Rows("5:7").Select
    Selection.EntireRow.Hidden = False
    Range("A1").Select
End If
End Sub

I know it's basic but i'm still learning, but at least it works

 

[The Boosh!]

ffs its still not right. It runs the macro no matter what selection I chose on the worksheet, I only want it to focus on the one cell

 

[Sarah]

record the marco?

macros hurt my brain tbh

Are you sure that a macro is what is required to do the task

 

[The Boosh!]

Yes it needs to be programmed into the VBE. You cannot record it.

It's frustrating because I can do most things in the VBE, but i've never used the worksheet Change event procedure, and im not sure of the parameters i need to set for what I want to do in this case.

 

[Blighty]

Why not explain the whole scenario then it will be easier for someone to help

 

[JoedotwasT5phy]

hmmm, i can't see much wrong with the code - although its hard to judge without all the pieces.

 

[The Boosh!]

Done it. I had to dimension a variable and add a few more lines.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCell As Range

Set KeyCell = Range("F10")

If Not Application.Intersect(KeyCell, Range(Target.Address)) Is Nothing Then

If KeyCell.Value = "QVN" Then
Rows("25:26").Select
Selection.EntireRow.Hidden = False
Range("f10").Select

Else
If KeyCell.Value <> "QVN" Then
Rows("25:26").Select
Selection.EntireRow.Hidden = True
Range("f10").Select
End If
End If
End If
End Sub

Thanks anyway everyone. I now know how to PM in the future

 

[Sym0n]

You have gone up in my estimations symondo!

My years of training are finally paying off!

 

[The Boosh!]

In all seriousness though are you fairly good with it or do you just dabble?

 

[Sym0n]

I have to use it most days at work mate for some annoying and complex stuff. But I do use google to help me a hell of a lot.