First project won't run--Named Ranges? Option button values?

[ShellyS]

Mr. Dan was most helpful with my first question. I'm reposting this as a separate subject, well, because it is, and to try to elicit a response. Mr. Dan gave me some tips on using checkboxes. The stakeholder insists on radio buttons instead of checkboxes, but I tried to follow the rest of his logic, and it made the code much cleaner.

Now it just won't run, and I don't know why. I think it is because I'm calling out named ranges incorrectly, but this is my first try at Visual Basic, so maybe it's something completely different. Should I be using a Property Let or Property Get statement instead of trying to set variable values based on the contents of a named range? Any tips or helpful hints or insights into why my code doesn't work? (I called out everything as public so it would show up in the macro selection box while I'm working. When the code is working, I'll make those Publics into Dim or Private statements except for the variable declarations).

Thank you very much,

Shelly S.
----------------------------------------------
Option Explicit
Option Base 1

' Path option buttons:

Public BOP1Path As OptionButton
Public BOP2Path As OptionButton
Public HPHybridPath As OptionButton
Public GHybridPath As OptionButton


' Crawlspace option buttons:

Public CrawlYes As OptionButton
Public CrawlNo As OptionButton

' Hot water option buttons:

Public StdDHW As OptionButton
Public EffDHW As OptionButton

' If Gas Hybrid system, "bin" variable to hold AFUE correction.

Public AFUEBin As Byte

' Math fields. Value set depending on radio button selected.

Public PathValue As Byte
Public CrawlValue As Byte
Public DHWValue As Byte
Public SumValue As Byte
Public SetUo As Byte


Public Sub FeedButtons()

' Assign worksheet cells to Option Button variables.

Set BOP1Path.Value = ActiveSheet.Range("TradeOff Worksheet!BOP1")
Set BOP2Path.Value = ActiveSheet.Range("TradeOff Worksheet!BOP2")
Set HPHybridPath.Value = ActiveSheet.Range("TradeOff Worksheet!HPHybrid")
Set GHybridPath.Value = ActiveSheet.Range("TradeOff Worksheet!GHybrid")

Set CrawlYes.Value = ActiveSheet.Range("TradeOff Worksheet!CrawlY")
Set CrawlNo.Value = ActiveSheet.Range("TradeOff Worksheet!CrawlN")

Set StdDHW.Value = ActiveSheet.Range("Tradeoff Worksheet.xls!StdWater")
Set EffDHW.Value = ActiveSheet.Range("Tradeoff Worksheet.xls!EffWater")

' Reads button settings and feeds value variables.

If BOP1Path = True Then PathValue = 1
If BOP2Path = True Then PathValue = 2
If HPHybridPath = True Then PathValue = 3
If GHybridPath = True Then PathValue = 4

If CrawlYes = True Then CrawlValue = 1
If CrawlNo = True Then CrawlValue = 10

If StdDHW = True Then DHWValue = 1
If EffDHW = True Then DHWValue = 2

' Populates the AFUEBin variable(used in index value calculation)

If Range("Tradeoff Worksheet.xls!AFUE") >= 0.7 & Range("Tradeoff Worksheet.xls!AFUE") < 0.74 Then AFUEBin = 0
If Range("Tradeoff Worksheet.xls!AFUE") >= 0.74 & Range("Tradeoff Worksheet.xls!AFUE") < 0.76 Then AFUEBin = 0.0033
If Range("Tradeoff Worksheet.xls!AFUE") > 0.76 Then AFUEBin = 0.0046
If Range("Tradeoff Worksheet.xls!AFUE") < 0.7 Then MsgBox ("The gas hybrid path requires an equipment efficiency of at least 0.70.")

Call DoMath

End Sub
Public Sub GrayStdDHW()

' Grays out standard hot water tank option in hybrid systems.

If PathValue = 3 Then StdDHW.Enabled = False
If PathValue = 4 Then StdDHW.Enabled = False

End Sub

Public Function DoMath()

'Sets SumValue to the sum of the three Value fields

SumValue = PathValue + CrawlValue + DHWValue

Call TargetUo(SumValue)

End Function

Public Sub TargetUo(SumValue)

'Looks up the target Uo value based on SumValue

Select Case SumValue

Case 2
Range("Tradeoff Worksheet.xls!TargetUo") = Range("Weightings.xls!Sum2")

Case 3
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum3")

Case 4
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum4")

Case 5
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum5a + AFUEBin")

Case 6
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum6")

Case 12
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum12")

Case 13
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum13")

Case 14
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum14")

Case 15
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum15a + AFUEBin")

Case 16
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum16")

End Select

End Sub

 

[BrianB]

"Won't run" is not enough information.
What do you want to happen ?
What actually happens ?
If it stops - Which error message do you get on which line ?

'-This line (and similar) has errors :-

Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum5a + AFUEBin") 

' (Try to avoid "lazy programming", 
' it will form good habits for larger projects)
'- should probably be :-

Workbooks("Weightings.xls"). _
    Worksheets("TargetUo").Range("Sum5a").Value = _
    Workbooks("Tradeoff Worksheet.xls"). _
         Worksheets("Something").Range("Sum5a").Value + AFUEBin

 

[ShellyS]

First project - update

Yay! You solved the riddle. (Sorry for the delayed response; I was out of town last week). I must admit that I smarted a bit at the "lazy" designation. I've got some experience with VBScript and JavaScript from years ago, along with a bit of XML. But this is my first shot at Visual Basic. I tried the help file and even bought a book, and followed the example exactly. But the example didn't include the Workbook. and Worksheet. parts.

I actually got my code to run (it's posted under the subject "Select Case in VB"), but only by removing the named range references and using cell references instead. That's not only embarrassing because it screams "Rookie!", but named ranges also make the code easier to understand to someone else trying to figure out what I did. I like self-documenting code.

Anyway, thank you very much for your help. I'm going to try replacing the cell references with named ranges the way you showed me. It'll read much better.

Sincerely,

Shelly S.