Macro - Is there a better way to do this?

I

iDeals

Board Regular
Joined
Oct 22, 2008
Messages
236
Ok, so below is my macro.... however every time the ComboBox changes it takes forever to run through the "if" statements. Can anyone suggest a better way of doing this?

Code: 
Private Sub ComboBox1_Change()   
    Sheets("Sheet1").Range("B2:B1000").ClearContents
    If Sheets("Sheet1").Range("A1").Value = 9 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = True
    Image4.Visible = True
    Image5.Visible = True
    Image6.Visible = True
    Image7.Visible = True
    Image8.Visible = True
    Image9.Visible = True
    ElseIf Sheets("Sheet1").Range("A1").Value = 8 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = True
    Image4.Visible = True
    Image5.Visible = True
    Image6.Visible = True
    Image7.Visible = True
    Image8.Visible = True
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 7 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = True
    Image4.Visible = True
    Image5.Visible = True
    Image6.Visible = True
    Image7.Visible = True
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 6 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = True
    Image4.Visible = True
    Image5.Visible = True
    Image6.Visible = True
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 5 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = True
    Image4.Visible = True
    Image5.Visible = True
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet").Range("A1").Value = 4 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = True
    Image4.Visible = True
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 3 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = True
    Image4.Visible = False
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 2 Then
    Image1.Visible = True
    Image2.Visible = True
    Image3.Visible = False
    Image4.Visible = False
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value >= 1 Then
    Image1.Visible = True
    Image2.Visible = False
    Image3.Visible = False
    Image4.Visible = False
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    End If
    Range("A1").Value = 1
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
From a learners stand point i dont fully understand all the code however
the images are visible = true or false as the default so whichever it is you only need to change that

for instance if all images are visible to start with then you only need to make those images visible = false depending on the conditions set.

Also I am alitte surprised you are not getting an error when running this code as where value = 4 the sheet is not identified as ("Sheet1")
 
Upvote 0
There probably is a quicker way to do this. Following the other user's suggestions. Before you enter the chain of if statements, make all of the images visible. Then, within each nested if, only make the requisite images visible. Again, as the user noted, you've made a typo for when the value is 4.

Select case statements look more elegant, but from what I've read, is else if is faster. Sorry can't be of more immediate help
 
Upvote 0
Thanks for the suggestions! As far as the typo, in workbook the sheet is called something else, I just changed it to sheet1 when I pasted the code for simplicity's sake. I will try taking out the "True" statements since they are inherent in the image and see if that speeds it up.

OKAY, here is what I tried:
Code: 
Private Sub ComboBox1_Change()
    Sheets("Sheet1").Range("B2:B1000").ClearContents
    If Sheets("Sheet1").Range("A1").Value = 8 Then
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 7 Then
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 6 Then
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 5 Then
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 4 Then
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 3 Then
    Image4.Visible = False
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value = 2 Then
    Image3.Visible = False
    Image4.Visible = False
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    ElseIf Sheets("Sheet1").Range("A1").Value >= 1 Then
    Image2.Visible = False
    Image3.Visible = False
    Image4.Visible = False
    Image5.Visible = False
    Image6.Visible = False
    Image7.Visible = False
    Image8.Visible = False
    Image9.Visible = False
    End If
    Range("A1").Value = 1
End Sub
This did speed it up marginally, but the new problem is that if the combo selects say "1" images 2-9 are "Visible = False" now if I change the combo to say "3" image 2 & 3 do not become visible again.
 
Last edited:
Upvote 0
I can't be sure, but I think you're slow-down is due to screen refresh.

Also, you haven't uncovered all images first. Review/try the following and let me know how you go:

Code: 
Private Sub ComboBox1_Change()
 
'temp variable
[INDENT]dim iTrigger as integer
[/INDENT]'turn of screen updates temporarily
[INDENT]application.screenupdating = false
[/INDENT]'clear area:     
[INDENT]Sheets("Sheet1").Range("B2:B1000").ClearContents
[/INDENT]'make all images visible
[INDENT]Image1.Visible = True
 Image2.Visible = True
 Image3.Visible = True
 Image4.Visible = True
 Image5.Visible = True 
 Image6.Visible = True
 Image7.Visible = True 
 Image8.Visible = True
 Image9.Visible = True
[/INDENT]'now uncover only those images needed
    iTrigger = Sheets("Sheet1").Range("A1").Value 
'enter the if structure to decide what to hide
    If iTrigger = 8 Then
     Image9.Visible = False
    ElseIf iTrigger = 7 Then
     Image8.Visible = False
     Image9.Visible = False
    ElseIf iTrigger = 6 Then
     Image7.Visible = False
     Image8.Visible = False
     Image9.Visible = False
    ElseIf iTrigger = 5 Then
     Image6.Visible = False
     Image7.Visible = False
     Image8.Visible = False
     Image9.Visible = False
    ElseIf iTrigger = 4 Then
     Image5.Visible = False
     Image6.Visible = False
     Image7.Visible = False
     Image8.Visible = False
     Image9.Visible = False
    ElseIf iTrigger = 3 Then
     Image4.Visible = False
     Image5.Visible = False
     Image6.Visible = False
     Image7.Visible = False
     Image8.Visible = False
     Image9.Visible = False
    ElseIf iTrigger = 2 Then
     Image3.Visible = False
     Image4.Visible = False
 Image5.Visible = False
     Image6.Visible = False
     mage7.Visible = False
     Image8.Visible = False
     Image9.Visible = False
    ElseIf iTrigger >= 1 Then
     Image2.Visible = False
     Image3.Visible = False
     Image4.Visible = False
     Image5.Visible = False
     Image6.Visible = False
     Image7.Visible = False
     Image8.Visible = False
     Image9.Visible = False
    End If
 
'restore screen refresh
application.screenupdating = true
 
End sub
 
Upvote 0
freesurf said:
I can't be sure, but I think you're slow-down is due to screen refresh.

Also, you haven't uncovered all images first. Review/try the following and let me know how you go:
Click to expand...

That works beautifully. the only issue was when the combo = 9, but I can fix that. The change is almost instant now!!!

Thanks for the help!
 
Upvote 0
Another way to speed it up would be to use a Select Case statement, that way it doesn't have to go through so many IF statements:

Code: 
Private Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("B2:B1000").ClearContents
    Select Case Sheets("Sheet1").Range("A1").Value
        Case 8
            Image9.Visible = False
        Case 7
            Image8.Visible = False
            Image9.Visible = False
        Case 6
            Image7.Visible = False
            Image8.Visible = False
            Image9.Visible = False
        Case 5
            Image6.Visible = False
            Image7.Visible = False
            Image8.Visible = False
            Image9.Visible = False
        Case 4
            Image5.Visible = False
            Image6.Visible = False
            Image7.Visible = False
            Image8.Visible = False
            Image9.Visible = False
        Case 3
            Image4.Visible = False
            Image5.Visible = False
            Image6.Visible = False
            Image7.Visible = False
            Image8.Visible = False
            Image9.Visible = False
        Case 2
            Image3.Visible = False
            Image4.Visible = False
            Image5.Visible = False
            Image6.Visible = False
            Image7.Visible = False
            Image8.Visible = False
            Image9.Visible = False
        Case Else
            Image2.Visible = False
            Image3.Visible = False
            Image4.Visible = False
            Image5.Visible = False
            Image6.Visible = False
            Image7.Visible = False
            Image8.Visible = False
            Image9.Visible = False
    End Select
    Range("A1").Value = 1
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks! I'll try that one as well... I'll be adding quite a bit more complexity to the change in the combobox event so one or the other should keep everything running smoothly. Very much appreciated!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Code stopped working
Replies
1
Views
287
rlv01
R
R
  • Question Question
Running one macro in several sheets by a code
2
Replies
12
Views
290
MARK858
MARK858
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
539
MagicCarpet
M
M
  • Question Question
Not running code
Replies
0
Views
212
MagicCarpet
M
E
  • Question Question
Can you combine these 2 macros ?
Replies
6
Views
216
excel4us
E

Forum statistics

Threads
1,224,581
Messages
6,179,668
Members
452,936
Latest member
anamikabhargaw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top