PDA

View Full Version : [SOLVED] Need help integrating checkboxes into existing (simple) macro



malde
04-28-2015, 04:56 AM
Hej guys, i'm a complete noob when talking VBA and have little to no experience in it. During my engineering internship however the uses and possibilities attached to using VBA for my assignment, are becoming increasingly apparent, so I've begun looking into how it works and how I can in cooperate it.

Basically my current problem is that I have written a simple GoalSeek macro which inturn is attached to a button:


Sub Temperatur_jaevner_Tabel_2()


' Keyboard Shortcut: Ctrl+m


'Tabel_2_Temperatur_Kedel
Range("Anlægsskitse!U83").GoalSeek GOal:=0, ChangingCell:=Range("Anlægsskitse!T40")
'Tabel_2_Temperatur_returrør
Range("Anlægsskitse!U84").GoalSeek GOal:=0, ChangingCell:=Range("Anlægsskitse!T41")
'Tabel_2_Temperatur_konv._m._snegle
Range("Anlægsskitse!U85").GoalSeek GOal:=0, ChangingCell:=Range("Anlægsskitse!T42")
'Tabel_2_Temperatur_eco._m._snegle
Range("Anlægsskitse!U86").GoalSeek GOal:=0, ChangingCell:=Range("Anlægsskitse!T43")
'Tabel_2_Temperatur_LUVO_m._snegle
Range("Anlægsskitse!U87").GoalSeek GOal:=0, ChangingCell:=Range("Anlægsskitse!T38")


End Sub

What I would like to do is insert a checkbox next to each of the ChangingCell (T38, T40, T41...) which controls whether the GoalSeek line beneath is run. So lets say if checkbox next to T40 is unchecked and the rest checked, it would skip "'Tabel_2_Temperatur_Kedel" (T40) and run the rest.

I've tried during it myself, adding a ActiveX checkbox to the worksheet and added the following to the VBA code:


'Tabel_2_Temperatur_Kedel


If CheckBox1 = True Then
Range("Anlægsskitse!U83").GoalSeek GOal:=0, ChangingCell:=Range("Anlægsskitse!T40")
ElseIf CheckBox1 = False Then
End If

'Tabel_2_Temperatur_returrør
Range("Anlægsskitse!U84").GoalSeek GOal:=0, ChangingCell:=Range("Anlægsskitse!T41")
'Tabel_2_Temperatur_konv._m._snegle
Range("Anlægsskitse!U85").GoalSeek GOal:=0, ChangingCell:=.............


But this is not working. So what am i doing wrong? Any help is appreciated :-)

EDIT: To clarify a little, heres what the checkboxes as i imagined them next to the respective T.cells

13270

p45cal
04-28-2015, 02:57 PM
Sub Temperatur_jaevner_Tabel_2()
' Keyboard Shortcut: Ctrl+m
With Sheets("Anlægsskitse")
'Tabel_2_Temperatur_Kedel
If .CheckBox1 Then .Range("U83").GoalSeek GOal:=0, ChangingCell:=.Range("T40")
'Tabel_2_Temperatur_returrør
If .CheckBox2 Then .Range("U84").GoalSeek GOal:=0, ChangingCell:=.Range("T41")
'Tabel_2_Temperatur_konv._m._snegle
If .CheckBox3 Then .Range("U85").GoalSeek GOal:=0, ChangingCell:=.Range("T42")
'Tabel_2_Temperatur_eco._m._snegle
If .CheckBox4 Then .Range("U86").GoalSeek GOal:=0, ChangingCell:=.Range("T43")
'Tabel_2_Temperatur_LUVO_m._snegle
If .CheckBox5 Then .Range("U87").GoalSeek GOal:=0, ChangingCell:=.Range("T38")
End With
End Sub

malde
04-28-2015, 11:36 PM
Sub Temperatur_jaevner_Tabel_2()
' Keyboard Shortcut: Ctrl+m
With Sheets("Anlægsskitse")
'Tabel_2_Temperatur_Kedel
If .CheckBox1 Then .Range("U83").GoalSeek GOal:=0, ChangingCell:=.Range("T40")
'Tabel_2_Temperatur_returrør
If .CheckBox2 Then .Range("U84").GoalSeek GOal:=0, ChangingCell:=.Range("T41")
'Tabel_2_Temperatur_konv._m._snegle
If .CheckBox3 Then .Range("U85").GoalSeek GOal:=0, ChangingCell:=.Range("T42")
'Tabel_2_Temperatur_eco._m._snegle
If .CheckBox4 Then .Range("U86").GoalSeek GOal:=0, ChangingCell:=.Range("T43")
'Tabel_2_Temperatur_LUVO_m._snegle
If .CheckBox5 Then .Range("U87").GoalSeek GOal:=0, ChangingCell:=.Range("T38")
End With
End Sub


Thanks p45 worked like a charm :-)