checkbox takes a LONG TIME to "register"

mortgageman

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Hello all:

I have ten checkboxes. Some of them execute immediately. Some of them take good 10-20 seconds to see the change on the screen. this is one that takes a LONG time. Is there something that I am doing that could explain it?

Thanks,



Code: 
Private Sub Checkbox4_change()
Dim i As Long
Dim c4string As String
     If CheckBox4.Value Then
         For i = 1 To 20
           Me.Controls("checkbox" & i).Value = False
         Next
         CheckBox4.Value = True
        
     
        TextBox7.Visible = True
        TextBox6.Visible = True
        TextBox5.Visible = True
        TextBox4.Visible = True
        
        TextBox1.Top = 42
        TextBox1.Height = 20
        TextBox4.Top = 70
        TextBox4.Height = 80
        TextBox5.Top = 160
        TextBox5.Height = 20
        'TextBox6.Top = 348
        'TextBox6.Height = height2add
        'TextBox7.Top = 456
        'TextBox7.Height = height2add
        'TextBox1.ZOrder
        
        
        
         With Me.TextBox1
         .Value = Range("Exodus4a").Value
         .Font.Bold = Range("exodus4a").Font.Bold
         .Font.Size = 20
         .ForeColor = RGB(255, 0, 0) ' RED
         End With
                  
         With Me.TextBox4
          c4string = Range("Exodus4B").Value & Range("exodus4c").Value & Range("exodus4d").Value
          c4string = c4string & Range("exodus4e").Value
          .Value = c4string
         .Font.Bold = Range("exodus4a").Font.Bold
         .Font.Size = 20
         .ForeColor = RGB(0, 0, 0)
         End With
      End If
         
End Sub


Gene Klein
 
Sort by date Sort by votes
<font face=Courier New><SPAN style="color:#007F00">' This goes at the very top of the UserForm code module in the Declarations area</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ufEventsDisabled <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()<br>     <SPAN style="color:#00007F">If</SPAN> ufEventsDisabled <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <br>     <SPAN style="color:#007F00">'do other stuff here...</SPAN><br>     <br>     ufEventsDisabled = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox2_Click()<br>     <SPAN style="color:#00007F">If</SPAN> ufEventsDisabled <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <br>     <SPAN style="color:#007F00">'do other stuff here...</SPAN><br>     <br>     ufEventsDisabled = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox3_Click()<br>     <SPAN style="color:#00007F">If</SPAN> ufEventsDisabled <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <br>     <SPAN style="color:#007F00">'do other stuff here...</SPAN><br>     <br>     ufEventsDisabled = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox4_Click()<br>     <br>     <SPAN style="color:#00007F">If</SPAN> ufEventsDisabled <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>     <br>     ufEventsDisabled = <SPAN style="color:#00007F">True</SPAN><br>     <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 20<br>        <SPAN style="color:#00007F">If</SPAN> i <> 4 <SPAN style="color:#00007F">Then</SPAN> Me.Controls("checkbox" & i).Value = <SPAN style="color:#00007F">False</SPAN><br>     <SPAN style="color:#00007F">Next</SPAN><br>    <br>     <SPAN style="color:#007F00">'do other stuff here...</SPAN><br>     <br>     ufEventsDisabled = <SPAN style="color:#00007F">False</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'</SPAN><br></FONT>
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
AlphaFrog said:
' This goes at the very top of the UserForm code module in the Declarations area
Dim ufEventsDisabled As Boolean

Private Sub CheckBox1_Click()
If ufEventsDisabled Then Exit Sub

'do other stuff here...

ufEventsDisabled = False
End Sub

Private Sub CheckBox2_Click()
If ufEventsDisabled Then Exit Sub

'do other stuff here...

ufEventsDisabled = False
End Sub

Private Sub CheckBox3_Click()
If ufEventsDisabled Then Exit Sub

'do other stuff here...

ufEventsDisabled = False
End Sub

Private Sub CheckBox4_Click()

If ufEventsDisabled Then Exit Sub

ufEventsDisabled = True
For i = 1 To 20
If i <> 4 Then Me.Controls("checkbox" & i).Value = False
Next

'do other stuff here...

ufEventsDisabled = False

End Sub

'
'
'
Click to expand...

Right - I understood what to do with the code - thank you for that anyway. My point is that the if statement on top "fails" and does not exit the sub. It then does whatever it making it take so long. The If statement not failing would do nothing good either, since it would exit the sub and NOT do any of the code that I want.

Gene Klein
 
Upvote 0
Try this.
Make a UF with two checkboxes and this code. Note the userform's caption as you click the boxes

Code: 
Dim ufEventsDisabled as Boolean

Private Sub CheckBox1_Click()
    If ufEventsDisabled Then Exit Sub
    
    ufEventsDisabled = True
    CheckBox2.Value = Not (CheckBox1.Value)
    Me.Caption = Val(Me.Caption) + 1
    
    ufEventsDisabled = False
End Sub

Private Sub CheckBox2_Click()
    If ufEventsDisabled Then Exit Sub
    
    ufEventsDisabled = True
    CheckBox1.Value = Not (CheckBox2.Value)
    Me.Caption = Val(Me.Caption) + 1
    
    ufEventsDisabled = False
End Sub
Then, comment out all the ufEventsDisabled lines and run it again.
Code: 
Private Sub CheckBox1_Click()
    'If ufEventsDisabled Then Exit Sub
    
    'ufEventsDisabled = True
    CheckBox2.Value = Not (CheckBox1.Value)
    Me.Caption = Val(Me.Caption) + 1
    
    'ufEventsDisabled = False
End Sub

Private Sub CheckBox2_Click()
    'If ufEventsDisabled Then Exit Sub
    
    'ufEventsDisabled = True
    CheckBox1.Value = Not (CheckBox2.Value)
    Me.Caption = Val(Me.Caption) + 1
    
    'ufEventsDisabled = False
End Sub
The first code runs approx twice as fast as the second.
 
Upvote 0
FWIW - I did try your solution, to no avail. I have isolated the offending lines:
Code: 
For i = 1 To 20
           Me.Controls("checkbox" & i).Value = False
         Next

When I comment them out, everything happens without any delay. Can those lines be altered in some way?

Gene Klein






mikerickson said:
Try this.
Make a UF with two checkboxes and this code. Note the userform's caption as you click the boxes

Code: 
Dim ufEventsDisabled as Boolean
 
Private Sub CheckBox1_Click()
    If ufEventsDisabled Then Exit Sub
 
    ufEventsDisabled = True
    CheckBox2.Value = Not (CheckBox1.Value)
    Me.Caption = Val(Me.Caption) + 1
 
    ufEventsDisabled = False
End Sub
 
Private Sub CheckBox2_Click()
    If ufEventsDisabled Then Exit Sub
 
    ufEventsDisabled = True
    CheckBox1.Value = Not (CheckBox2.Value)
    Me.Caption = Val(Me.Caption) + 1
 
    ufEventsDisabled = False
End Sub
Then, comment out all the ufEventsDisabled lines and run it again.
Code: 
Private Sub CheckBox1_Click()
    'If ufEventsDisabled Then Exit Sub
 
    'ufEventsDisabled = True
    CheckBox2.Value = Not (CheckBox1.Value)
    Me.Caption = Val(Me.Caption) + 1
 
    'ufEventsDisabled = False
End Sub
 
Private Sub CheckBox2_Click()
    'If ufEventsDisabled Then Exit Sub
 
    'ufEventsDisabled = True
    CheckBox1.Value = Not (CheckBox2.Value)
    Me.Caption = Val(Me.Caption) + 1
 
    'ufEventsDisabled = False
End Sub
The first code runs approx twice as fast as the second.
Click to expand...
 
Upvote 0
Code: 
ufEventsDisabled = True
For i = 1 To 20
    Me.Controls("checkbox" & i).Value = False
Next i
ufEventsDisabled = False
 
Upvote 0
mikerickson said:
Code: 
ufEventsDisabled = True
For i = 1 To 20
    Me.Controls("checkbox" & i).Value = False
Next i
ufEventsDisabled = False
Click to expand...

Mike, thanks but this did not do anything. Well - better to say that I don't know what it is doing, but it is NOT decreasing the delay.

Gene Klein
 
Upvote 0
Are you putting
Code: 
If ufEventsDisabled Then Exit Sub
in all the Change or Click events for CheckBox1 - CheckBox20?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Date on userform shows tomorros date NOT todays
Replies
1
Views
196
ipbr21054
I
C
  • Question Question
Linking two sheets in different workbooks takes a long time even with only 500 rows of data. Is there a quicker, more efficient way to do this?
Replies
0
Views
178
chazrab
C
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
114
6StringJazzer
6StringJazzer
K
  • Question Question
Help Needed
2
Replies
13
Views
334
kwavic
K
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,014
Latest member
Chris258

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