VBA newbie question - Multiple strings on one sheet?

[Cyclops755]

I'm very new to VBA, and have only used it lightly so far, but I'm trying to use it more, and have encountered a problem when trying to have two different "strings" of code on one sheet. My code currently is:

Private Sub Worksheet_Change(ByVal Target As Range)

' Limit to only work on columns 9 or 10
If Target.Column = 9 Or Target.Column = 10 Then
Application.EnableEvents = False
If Target.Column = 9 Then Target.Offset(0, 1).Value = 1 - Target.Value
If Target.Column = 10 Then Target.Offset(0, -1).Value = 1 - Target.Value
Application.EnableEvents = True
End If

End Sub

Code for Worksheet "Cells as Checkboxes"

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("checkboxes")) Is Nothing Then Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
Exit Sub
End If
End Sub

The first part ensures that the percentages in a pair of columns always adds up to 100%, the other allows you to check boxes by double-clicking them. I've heard that each might need to be put in a module? but I haven't been able to make that work. Can someone help clear this up?

 

[Ruddles]

Assuming that code all relates to a single worksheet, all of that code from the first Private to the last End Sub needs to go in the code module for that worksheet. They are 'event handlers' for a worksheet and they will not work anywhere else. In VBA, go Ctrl-R to view the Project Explorer, then double-click the name of the worksheet you want those bits of code to work with. The code should be in that code module.

Where did you find them? Are they working at the moment? If they are both working okay, leave them where they are - if you move them you will stop them from working.

(The directive Option Explicit goes at the very top of the module.)

 

[Cyclops755]

Thanks for clarifying Ruddles, yes its all from the same worksheet. I found the code from other threads started to solve similar problems, and pasted them in. Both worked flawlessly alone, but together not so much. I believe I put them in the place you are describing, as I opened the page associated with the specific worksheet. I was confused between placing each one directly in the worksheet, or having to create "modules" at the bottom of the project explorer and somehow call the modules in on the worksheet page. Me and a friend seemed to have found a way to make them both coexist, and they're functioning normally for now. Here's what we have:

Sub Worksheet_Change(ByVal Target As Range)

' Limit to only work on columns 9 or 10
If Target.Column = 9 Or Target.Column = 10 Then
Application.EnableEvents = False
If Target.Column = 9 Then Target.Offset(0, 1).Value = 1 - Target.Value
If Target.Column = 10 Then Target.Offset(0, -1).Value = 1 - Target.Value
Application.EnableEvents = True
End If

End Sub



Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("checkboxes")) Is Nothing Then Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
Exit Sub
End If
End Sub


Not sure if thats the best/"correct" way to do it, but it works . If you have any tips or advice on a better way to do this, I'd love to know. Thanks again!

 

[Smitty]

Welcome to the Board!

Worksheet level code, like events, goes in the worksheet specific module, regardless of how many events there are. The problems begin when you try to have two of the same event instead of consolidating them.

So what you did above is correct.

HTH,