New to VB for excel..

[ternelson]

Hi All,

So i just started learning excel and the vb part of it, im getting some of it but not all of it... what im trying to do is take a single word like blue and have it add 1 to a cell that has a number... ie if i have 15 in B15 and then change H2 to blue i would like it to add 1 to 15 to make it 16... or have it add it to 2 different cells.. ie if i have 15 in B15 and 12 in B19 and change H2 to red that it would add 1 to both B15 and B19... hope i have explained it right... thanks in advance for any help..

Terry

 

[jproffer]

Right click on the sheet tab and select "View Code". Paste this into the white area on the right. Come back with any more question...and welcome to the board.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H2")) Is Nothing Then
    Select Case Target.Value
        Case "Blue"
            Range("B15").Value = Range("B15").Value + 1
        Case "Red"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
        'add more cases as needed
    End Select
End If
End Sub

 

[ternelson]

That did the trick thank you so much... ive been trying to figure that out for over a week now ... i was trying to do it as a formula but wasnt having any luck so a co-worker suggested that i try using VB.. im sure i'll end up getting stuck down the road ... thanks again for the help ...

Terry

Right click on the sheet tab and select "View Code". Paste this into the white area on the right. Come back with any more question...and welcome to the board.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H2")) Is Nothing Then
    Select Case Target.Value
        Case "Blue"
            Range("B15").Value = Range("B15").Value + 1
        Case "Red"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
        'add more cases as needed
    End Select
End If
End Sub

 

[ternelson]

so here is an added twist for you... if i then wanted to have say K2 pull from a list on the 2nd sheet how would that work?? ie if i selected blue and it added the number to my cells but then wanted K2 to allow me to select from a list of items ( slime, solid, wood, etc) from a range of B2 to B10 on sheet 2 can it be done??

thanks again in advance ..

Terry

 

[jproffer]

It can absolutely be done. So if you select "Blue" on sheet 1, you want K2 on sheet 1 to pick from a list of 9 things on sheets 2, B2:B10...got that part. How does K2 know which of the 9 things you want? Is there a table somewhere with (blue, red, etc) vs. (slime, solid, wood, etc)? Or is it based on the numbers you're changing in B15 and B19?

 

[ternelson]

So it would be a table, B2 Wood,B3 Slime, etc then what i would like to have happen is a drop down in K2 so that when i chose blue for H2 it only gives me the option of picking from my list of itmes in B2 - B10 on the second sheet.. the numbers in the B15 and B19 would not be affected... i hope thats coming out right on what im asking for...

It can absolutely be done. So if you select "Blue" on sheet 1, you want K2 on sheet 1 to pick from a list of 9 things on sheets 2, B2:B10...got that part. How does K2 know which of the 9 things you want? Is there a table somewhere with (blue, red, etc) vs. (slime, solid, wood, etc)? Or is it based on the numbers you're changing in B15 and B19?

 

[jproffer]

I think I'm pickin' up what you're puttin' down . Try this in place of the code above...not in addition to, but replace your current sheet code from above with this....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H2")) Is Nothing Then
    Select Case Target.Value
        Case "Blue"
            Range("B15").Value = Range("B15").Value + 1
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$B$2:$B$10" 'this line and the 2 above it act as one line of code.
                                               'Add more to Red and other colors as desired.
                                               'I'll start one and comment it out.
        Case "Red"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
            'Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$B$2:$B$10" '<--Change drop-down range here
                
        'add more cases as needed
    End Select
End If
End Sub

 

[ternelson]

It works like a champ... let me play a little more with the sheet and seee if there is something i can come up with to stump you... hahahaha... great work this coding is new to me but im liking it... thanks for the current help... i know i'll be back with more soon ...

Terry

I think I'm pickin' up what you're puttin' down . Try this in place of the code above...not in addition to, but replace your current sheet code from above with this....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H2")) Is Nothing Then
    Select Case Target.Value
        Case "Blue"
            Range("B15").Value = Range("B15").Value + 1
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$B$2:$B$10" 'this line and the 2 above it act as one line of code.
                                               'Add more to Red and other colors as desired.
                                               'I'll start one and comment it out.
        Case "Red"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
            'Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$B$2:$B$10" '<--Change drop-down range here
                
        'add more cases as needed
    End Select
End If
End Sub

 

[jproffer]

It's not hard to stump me ...you just happened to hit on one of the few things I can figure out lol.

Glad it worked for you.

On a side note, I just noticed you're in Union, MO...I'm in Ste Gen right now (not REAL close, but kinda close) at work and live in Chester, IL ....home of Popeye (one of many claimed "home(s) of Popeye"), for whatever that's worth.

 

[ternelson]

Not that far, im close to 6 flags,, ok so it worked until i added the drop down range for red, then i got a runtime error 1004 Application-defined or object-defined error, when i hit debug the following info comes up in yellow with the arrow pointing to the Formula line
Range("K2").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=Sheet2!$B$2:$B$10"

this is what i have in my worksheet..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H2")) Is Nothing Then
Select Case Target.Value
Case "Blue"
Range("B15").Value = Range("B15").Value + 1
Range("K2").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=Sheet2!$B$2:$B$10" 'this line and the 2 above it act as one line of code.
'Add more to Red and other colors as desired.
'I'll start one and comment it out.
Case "Red"
Range("B15").Value = Range("B15").Value + 1
Range("B19").Value = Range("B19").Value + 1
Range("K2").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=Sheet2!$C$2:$C$10" '<--Change drop-down range here

'add more cases as needed
End Select
End If
End Sub