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]

That was my fault. You gotta remove old validation before adding new validation.

Try this one:

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.Delete
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$B$2:$B$10"
        Case "Red"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
            Range("K2").Validation.Delete
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$C$2:$C$10" '<--Change drop-down range here
    End Select
End If
End Sub

Basically (I think) the reason is, you can't add (Validation.Add...) the same type (list in this case) of validation twice to a given cell, so to change it the first "Add" has to be removed...then the new "Add" can happen.

 

[ternelson]

That did the trick .... might take a bit for this old brain to get it all worked out but its getting there .... again thank you for your help...

That was my fault. You gotta remove old validation before adding new validation.

Try this one:

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.Delete
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$B$2:$B$10"
        Case "Red"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
            Range("K2").Validation.Delete
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$C$2:$C$10" '<--Change drop-down range here
    End Select
End If
End Sub

Basically (I think) the reason is, you can't add (Validation.Add...) the same type (list in this case) of validation twice to a given cell, so to change it the first "Add" has to be removed...then the new "Add" can happen.

 

[jproffer]

2 old brains are better than one.....well maybe .

No problem, glad it worked.

 

[ternelson]

My odd problem your great answers... that works pretty good .... hahahaha....

2 old brains are better than one.....well maybe .

No problem, glad it worked.

 

[ternelson]

Ok let me try to stump you with this one .... i want to pull one % from sheet2 and a 2nd % from sheet3 and a 3rd % from sheet3 and add them together on sheet1. can i put that in the current code i have or do i need a new sub?? ie F17(sheet1) = total D97(sheet2) B146 and B166(sheet3)... hope i explained that good enough... then the next part can i add a 2nd If Not Intersect(Target, Range("H2")) Is Nothing Then Select Case Target.Value or do i need to create a new sub for that ??? sorry for being a pain ....

 

[jproffer]

That sounds like more of a formula solution unless you have some compelling reason to use VBA...in F17, sheet 1 put this:

=AVERAGE(Sheet2!D97,Sheet3!B146,Sheet3!B166)

and see if it gives you what you want.

 

[ternelson]

that would work if it was just the 3 spots, i was not totally clear on what i have ... sorry about that .... i have set % starting on sheet2 from D97 to D113 and then E,F G and H all have set % in them as well... then to the Sheet3 i have2 more set % starting at B146 through B162 and then C through I with the same ranges, then starting B165 to B170 and then C through I with the same range... i know you dont have to do this so i thank you.. if this is too much of a pain i can stop being lazy and add them myself... hahahahahaha...

another question can i have 2 of the same types of Private Sub Worksheet_Change(ByVal Target As Range) on the same sheet???

 

[jproffer]

Q1) You just want to loop through the cells and put hard numbers in them one time? If the percentages change by certain increments as you step down the columns and across the rows, this wouldn't be too difficult and it wouldn't really be an event procedure, we would just put that into a standard code module and run it. If there is no pattern to them, it would be just as easy to just type them into the cells directly as each cell would have to have it's own line of code anyway.

Q2) No you can't, but you can (usually) incorporate anything more that you want to do into the same event...for instance a Worksheet_Change.

 

[ternelson]

how would i go about adding If Not Intersect(Target, Range("06")) to the current layout if it can be done?? the % tables are a bit odd i could post them if you would like to see how they are laid out, but as i think about it it might just be easier for a manual input because it ties in to several factors ...
thanks again for the help ... i would have never gotten as far without your help ...

Terry

Q1) You just want to loop through the cells and put hard numbers in them one time? If the percentages change by certain increments as you step down the columns and across the rows, this wouldn't be too difficult and it wouldn't really be an event procedure, we would just put that into a standard code module and run it. If there is no pattern to them, it would be just as easy to just type them into the cells directly as each cell would have to have it's own line of code anyway.

Q2) No you can't, but you can (usually) incorporate anything more that you want to do into the same event...for instance a Worksheet_Change.

 

[jproffer]

Couple different ways to do that. If that's the only range you need to add you could use something like 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.Delete
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$B$2:$B$10"
        Case "Red"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
            Range("K2").Validation.Delete
            Range("K2").Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=Sheet2!$C$2:$C$10" '<--Change drop-down range here
    End Select
End If

If Not Intersect(Target, Range("O6")) Is Nothing Then
    'do whatever you want to do if O6 is the target
End If

End Sub

If you need to add more ranges, this might be a better way:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$H$2"
        Select Case Target.Value
            Case "Blue"
                Range("B15").Value = Range("B15").Value + 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Sheet2!$B$2:$B$10"
            Case "Red"
                Range("B15").Value = Range("B15").Value + 1
                Range("B19").Value = Range("B19").Value + 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Sheet2!$C$2:$C$10" '<--Change drop-down range here
        End Select
        
    Case "$O$6"
        'do whatever you want to do if O6 is the target
        MsgBox "O6"
        
    Case "$A$1"
End Select

End Sub

When you start stacking Case Selects together like that, it becomes even more important to indent your code so you can follow along with what you have (and more importantly what you're missing in case of a Debug situation).

Both codes above do the exact same thing. Needless to say, the message box would be taken out after you have it coded to do what you want when O6 is the target...that was just for testing.

BTW, if you're going to Select Case the Target.Address, you'll have to include the dollar signs in each one. The address is just a text string (so it needs to be in quotes), but VBA always makes it an absolute Range ($A$1....$D$7....etc.) as opposed to a relative reference (A1...D7...etc.)