PDA

View Full Version : Basic VBA - Active X control to also link to cell value



dominiks
05-28-2013, 04:14 AM
Hi,

I know this may seem like a very basic question but I am really stuck on this one.

I have multiple active-x check-boxes and quite a few contain code to hide or display another work-book if checked or not, that is working fine but for one set of them I need some help with the code to make it do more.

I need to be able to hide or display a sheet when a check box is ticked but also when it conforms to value within another cell on the same sheet.

So the code that I am stuck with;

Public Sub CheckBox29_Click()
Range(O5) = "Schlumberger"
If CheckBox29.Value = True Then
Sheets("SLB TFO-Run1").Visible = True
Else
Sheets("SLB TFO-Run1").Visible = False
End If
End Sub

I need to specify that when the value of cell O5 is a word that the logic of the check box then decides which sheet to display. The same code would then be duplicated to allow 7 different words for O5.

Any help on this would be much appreciated.

Kind Regards

Dominik

snb
05-28-2013, 07:49 AM
Please use code tags !


Public Sub CheckBox29_Click()
Sheets("SLB TFO-Run1").Visible = CheckBox29.Value *(Range("O5").value = "Schlumberger")
End Sub

SamT
05-28-2013, 08:05 AM
Select Case Range("O5").Value
Case "Word1"
'Show/Hide Sheet1
Case "Word2"
'Do something Different
Case 3, 4, 5, 6, & 7
'All get their own "Case" lines
Case Else
'If none of the Above Cases apply
End Select

dominiks
05-30-2013, 12:05 AM
Thank you so much for your help on this one.

In the end I went with the following code which is working exactly as I need it to.

Public Sub CheckBox29_Click()
Sheets("SLB TFO-Run1").Visible = CheckBox29.Value * (Range("O5").Value = "Schlumberger")
Sheets("HAL TFO-Run1").Visible = CheckBox29.Value * (Range("O5").Value = "Halliburton")
Sheets("PTF TFO-Run1").Visible = CheckBox29.Value * (Range("O5").Value = "Pathfinder")
Sheets("BHI TFO-Run1").Visible = CheckBox29.Value * (Range("O5").Value = "Baker Hughes")
Sheets("WTF TFO-Run1").Visible = CheckBox29.Value * (Range("O5").Value = "Weatherford")
Sheets("GD-APS TFO-Run1").Visible = CheckBox29.Value * (Range("O5").Value = "Gyrodata/APS")
Sheets("XXT TFO-Run1").Visible = CheckBox29.Value * (Range("O5").Value = "XXT")
End Sub

Thank you again for you help on this one, I know its quite simple but I am new to VBA and its been driving me crazy trying to get this one to work.

Regards

Dominik