VBA for Check Box or Button

Viking1221

New Member
Joined
May 25, 2017
Messages
32
Hello All,

I am just beginning to learn about VBA and I have a file I am beginning to workon. Is there a specific reason to use a check box vs a button to hide or unhiderows? While on the subject what are the limitations to each object?


Can someone show me a simple macro that will hide allrows that have “LE1” in column A?

Thanks.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi

Not tested, straight from head:

Code:
Sub HideLE1Rows()
Dim cell As Range
For Each cell In [A1:A1000]
    If cell.Value = "LE1" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = True
Next cell
End Sub

if you want to hide rows with value like 'somethingLE1something' change if for:
Code:
    If cell.Value like "*LE1*" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = True
 
Upvote 0
Hi

Not tested, straight from head:

Code:
Sub HideLE1Rows()
Dim cell As Range
For Each cell In [A1:A1000]
    If cell.Value = "LE1" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = True
Next cell
End Sub

if you want to hide rows with value like 'somethingLE1something' change if for:
Code:
    If cell.Value like "*LE1*" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = True



Thank you that works great. I also want to see if I can make it work with a check box. What is wrong with this code:
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Dim cell As Range
For Each cell In [B1:B10000]
If cell.Value = "HTSU" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = True
Next cell
Else: End If
If CheckBox1.Value = False Then
Dim cell As Range
For Each cell In [B1:B10000]
If cell.Value = "HTSU" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = False
Next cell
End Sub
 
Upvote 0
Try this:

Private Sub CheckBox1_Click()
Dim cell As Range

If CheckBox1.Value = True Then

For Each cell In [B1:B10000]
If cell.Value = "HTSU" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = True
Next cell
Else: End If
If CheckBox1.Value = False Then
Dim cell As Range
For Each cell In [B1:B10000]
If cell.Value = "HTSU" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = False
Next cell
End Sub
 
Upvote 0
Ignore above (for some reason I can't edit).

Try this

Code:
Private Sub CheckBox1_Click()
Dim cell As Range
For Each cell In [B1:B10000]
    If CheckBox1.Value = True And cell.Value = "HTSU" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = True
    If CheckBox1.Value = False And cell.Value = "HTSU" Then Cells(cell.Row, cell.Column).EntireRow.Hidden = False
Next cell
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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