PDA

View Full Version : Solved: If Statement



Student1000
05-19-2010, 10:31 AM
hello everyone. I'm facing a problem with a rather long if statement.
All I want to do is to display 1 picture if the value in Calculations!BH = 1, two pictures if the value = 2 etc.

The "ListBox1.ListIndex + 3" just makes sure that the right cell in my spreadsheet is selected.

however it just won't work.

Would be nice if someone could help me.
cheers


If Range("Calculations!BH" & ListBox1.ListIndex + 3).Value = 1 Then
NightPic1.Visible = True And _
NightPic2.Visible = False And _
NightPic3.Visible = False And _
NightPic4.Visible = False And _
NightPic5.Visible = False And _
NightPic6.Visible = False

ElseIf Range("Calculations!BH" & ListBox1.ListIndex + 3).Value = 2 Then
NightPic1.Visible = True And _
NightPic2.Visible = True And _
NightPic3.Visible = False And _
NightPic4.Visible = False And _
NightPic5.Visible = False And _
NightPic6.Visible = False

ElseIf Range("Calculations!BH" & ListBox1.ListIndex + 3).Value = 3 Then
NightPic1.Visible = True And _
NightPic2.Visible = True And _
NightPic3.Visible = True And _
NightPic4.Visible = False And _
NightPic5.Visible = False And _
NightPic6.Visible = False

ElseIf Range("Calculations!BH" & ListBox1.ListIndex + 3).Value = 4 Then
NightPic1.Visible = True And _
NightPic2.Visible = True And _
NightPic3.Visible = True And _
NightPic4.Visible = True And _
NightPic5.Visible = False And _
NightPic6.Visible = False

ElseIf Range("Calculations!BH" & ListBox1.ListIndex + 3).Value = 5 Then
NightPic1.Visible = True And _
NightPic2.Visible = True And _
NightPic3.Visible = True And _
NightPic4.Visible = True And _
NightPic5.Visible = True And _
NightPic6.Visible = False
End If

mbarron
05-19-2010, 01:43 PM
What about something like this:

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To Sheet1.OLEObjects.Count
If Left(Sheet1.OLEObjects(i).Name, 5) = "Image" Then
If Val(Mid(Sheet1.OLEObjects(i).Name, 6)) <= Sheet1.ListBox1.ListIndex + 1 Then
Sheet1.OLEObjects(i).Visible = True
Else
Sheet1.OLEObjects(i).Visible = False
End If
End If
Next
End Sub

I've attached the file for demonstration purposes.

Student1000
05-20-2010, 02:36 AM
Could you maybe tell me what the names of the pictures are?
I do not really completely understand the code.

1) What does (Sheet.OLEObjects(i).Name ,5) do?
2) how can i specify the positioning of the pictures?
3) whats the name of the pictures?

would be really thankful for any answer.
thx

mbarron
05-20-2010, 09:12 AM
Could you maybe tell me what the names of the pictures are?
I do not really completely understand the code.

1) What does (Sheet.OLEObjects(i).Name ,5) do?
2) how can i specify the positioning of the pictures?
3) whats the name of the pictures?

would be really thankful for any answer.
thx

Your questions
1) This line checks to see if the OLEObject's name starts with image.
2)One way to move the pictures
With Sheet1.Image1
.Left = 400
.Top = 300
End With
3)The names of the pictures in my sample are Image1, Image2...Image6

Student1000
05-20-2010, 09:29 AM
thank you. know i modified it an it works!
cheers