VB Code Help

[kls]

I have checkboxes on Sheet 1 that populates Sheet 2; if the checkbox = true. Depending on the “text” of A5 on Sheet 2, then a listbox will populate cell D5 on Sheet 2.

The VB Code I'm using is:

If CheckBox9.Value = True Then
Worksheets("Sheet2").Range("A5").Value = "Air Emissions"
Worksheets("Sheet2").Range("B5").Value = "Point Source Air Emission"
Worksheets("Sheet2").Range("C5").Value = "CO2"
Else
Worksheets("Sheet2").Range("B5").ClearContents
End If
If Worksheets("Sheet2").Range("A5").Value = "Air Emissions" Then
Worksheets("Sheet2").Range("D5:D5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=AirEmissionsI"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End
End If
End Sub

The Code bombs at [Worksheets("Sheet2").Range("D5:D6").Select]. How do I state that I want to Select Sheet2, Range D5 without activating the sheet?

[NateO]

The following tends to be a little hardier than select:

Application.Goto Sheets("sheet2").[d5:d6]

But you could define the area and work with it without selecting:

Sub redo()

Dim n As Range

Set n = Sheets("sheet2").[d5:d6]

If CheckBox9.Value = True Then

Worksheets("Sheet2").Range("A5").Value = "Air Emissions"

Worksheets("Sheet2").Range("B5").Value = "Point Source Air Emission"

Worksheets("Sheet2").Range("C5").Value = "CO2"

Else

Worksheets("Sheet2").Range("B5").ClearContents

End If

If Worksheets("Sheet2").Range("A5").Value = "Air Emissions" Then

With n.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=AirEmissionsI"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

End

End If

End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-20 15:43 ]

[Mark O'Brien]

The Code bombs at [Worksheets("Sheet2").Range("D5:D6").Select]. How do I state that I want to Select Sheet2, Range D5 without activating the sheet?

Just a little addendum to Nate's sage advice.

You can't select a cell if the sheet is nnot activated. It's against the rules.

[kls]

Thank you for your reply...only now I'm getting an error: Application-defined or object-defined error at [.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=AirEmissionsI"].

I did not want to "activate" Sheet2 because I want to remain on Sheet1; not flip back & forth.

[NateO]

The way I wrote the code above (in part 2), you won't be selecting any cells. The data validation insert above works well for me except in the case where I don't have a range named AirEmissionsI. Better safe than sorry as well and capitalize it the same in both the range naming and vba procedure.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-20 20:09 ]

[kls]

I've checked everything and am still getting an error at the .Add Type:=xlValidateList...

If I use Application.Goto Sheets("sheet2").[d5:d6] as you suggested previously; that works; only it it changes my screen to sheet2. There are several checkboxes and I would like to stay on Sheet1 instead of flipping back & forth between the two sheets.

Any other suggestions??

[NateO]

No need to to select the sheet. Allow me to ask what:

"=AirEmissionsI"

represents? Should be a named range, if you hit f5 can you goto this range? If I set a cell with this as a named range, I can run the code above in xl2000.

[kls]

Yes, "=AirEmissionsI" is a named range and I can go to this range by hitting f5.

The range is on a different / separate sheet but I wouldn't think that matters.

I'm using XL2002

[NateO]

Because it's a named range, it doesn't matter. I rewrote the if to combine the first if with the 2nd. I have checkbox9 on the first sheet and "AirEmissionsI" on sheet3. The following works in Excel 2000 with no selecting (& fairly quickly):

Sub redo2()

Dim n As Range

Set n = Sheets("sheet2").Range("d5:d6")

If Sheets(1).CheckBox9.Value = True Then

    Worksheets("Sheet2").Range("A5").Value = "Air Emissions"

    Worksheets("Sheet2").Range("B5").Value = "Point Source Air Emission"

    Worksheets("Sheet2").Range("C5").Value = "CO2"



    With n.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

            Operator:=xlBetween, Formula1:="=AirEmissionsI"

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .ShowInput = True

        .ShowError = True

    End With



Else:

    Worksheets("Sheet2").Range("B5").ClearContents

End If



End Sub

I suppose it's possible that xl2002 won't use this methodology but it seems that XL has gotten more flexible with code over time. Wish I had xl2002 to test it with.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-21 13:47 ]

[kls]

I wish you had XL2002 also!!!

Thanks for your interest. Right now it's looks like my only alternative is to add a command button to add the listbox...