Hide lines in Excel

B

BasicUserWithExp

New Member
Joined
Feb 15, 2018
Messages
17
I am a rebar estimator, i have a workbook that contains a quote sheet, and a pricing sheet. i have multiple products, and i want to expand with more. I currently have my quote sheet setup to hide rows using a checkbox, the checkbox also gets hidden. There is a macro that resets the Hidden status of the lines and Check boxes.

I found some code that works for what i want however; i would like to add more lines for more products. it seems the more Check boxes i add the more errors i have.
1. no code errors show
2. multiple Check Boxes appear on top of each other
3. checkbox may hide the wrong line.
4. code may no reset all hidden lines and/or Check Boxes

I found the code online but i cannot find it again to check and see if i made an error somewhere.

Code: 
Sub HideRow()
  Dim ws As Worksheet
  Dim myCBX As CheckBox
  Dim lRow As Long


  Set ws = ActiveSheet
  Set myCBX = ws.CheckBoxes(Application.Caller)
  lRow = myCBX.TopLeftCell.Row


  Application.ScreenUpdating = False
  Select Case myCBX.Value
  Case 1   'box is checked
    ws.Rows(lRow).EntireRow.Hidden = True
    myCBX.Visible = False
  Case Else   'box is not checked
    ws.Rows(lRow).EntireRow.Hidden = False
    myCBX.Visible = True
  End Select
  Application.ScreenUpdating = True
End Sub


Sub sbHidingUnHideRows()


'To Hide Rows 22 to 25
Rows("11:33").EntireRow.Hidden = True


'To UnHide Rows 22 to 25
Rows("11:33").EntireRow.Hidden = False


End Sub


'Make all rows visible and uncheck all checkboxes
Sub UncheckAll()
  Dim ws As Worksheet
  Dim myCBX As Excel.CheckBox
  Dim lRow As Long


  Set ws = ActiveSheet
  Application.ScreenUpdating = False
  With ws
    For Each myCBX In .CheckBoxes
      myCBX.Value = xlOff
      lRow = myCBX.TopLeftCell.Row
      .Rows(lRow).EntireRow.Hidden = False
      myCBX.Visible = True
    Next myCBX
  End With
  Application.ScreenUpdating = True
End Sub

I am asking for suggestions on improvements with code and/or setup
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
I never think it's a good practice or a practical practice to have a large number of buttons or checkboxes on a sheet.

A lot of scripts can be run by double clicking on a cell. This requires very little code and requires no buttons.

The below script would hide any row if you double click on column A of that row.

So if you double click on Range(A45) then row 45 would be hidden.

And I added a way to unhide all rows.
Double click on Range("C1") to unhide all rows.

Would something like this work?

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  8/26/2019  4:45:30 AM  EDT
If Target.Column = 1 Then
    Cancel = True
    Rows(Target.Row).Hidden = True
End If
Cancel = True
If Target.Address = Range("C1").Address Then Rows.Hidden = False
End Sub
 
Upvote 0
Can i use this code to add text to a texbox?

Code: 
TextBox2.Text = ""


If CheckBox21.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R37").Value & " - " & Range("s28").Value & ", "
End If

instead of using checkbox, doubleclick a cell to add, then another cell to remove?

Currently:
Code: 
Private Sub CommandButton1_Click()


TextBox1.Text = ""


If CheckBox1.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R28").Value & " - " & Range("s28").Value & ", "
End If


If CheckBox2.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R29").Value & " - " & Range("S29").Value & ", "
End If


If CheckBox3.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R30").Value & " - " & Range("S30").Value & ", "
End If


If CheckBox4.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R31").Value & " - " & Range("S31").Value & ", "
End If


If CheckBox5.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R32").Value & " - " & Range("S32").Value & ", "
End If


If CheckBox6.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R33").Value & " - " & Range("S33").Value & ", "
End If


If CheckBox7.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R34").Value & " - " & Range("S34").Value & ", "
End If


If CheckBox8.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R35").Value & " - " & Range("S35").Value & ", "
End If


If CheckBox9.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R36").Value & " - " & Range("S36").Value & ", "
End If


If CheckBox10.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R37").Value & " - " & Range("S37").Value & ", "
End If


If CheckBox11.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R38").Value & " - " & Range("S38").Value & ", "
End If


If CheckBox12.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R39").Value & " - " & Range("S39").Value & ", "
End If


If CheckBox13.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R40").Value & " - " & Range("S40").Value & ", "
End If


If CheckBox14.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R41").Value & " - " & Range("S41").Value & ", "
End If


If CheckBox15.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R42").Value & " - " & Range("S42").Value & ", "
End If


If CheckBox16.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R43").Value & " - " & Range("S43").Value & ", "
End If


If CheckBox17.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R44").Value & " - " & Range("S44").Value & ", "
End If


If CheckBox18.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R45").Value & " - " & Range("S45").Value & ", "
End If


If CheckBox19.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R46").Value & " - " & Range("S46").Value & ", "
End If


If CheckBox20.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R47").Value & " - " & Range("S47").Value & ", "
End If




TextBox2.Text = ""


If CheckBox21.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R37").Value & " - " & Range("s28").Value & ", "
End If


If CheckBox22.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R38").Value & " - " & Range("S29").Value & ", "
End If


If CheckBox23.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R39").Value & " - " & Range("S30").Value & ", "
End If


If CheckBox24.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R40").Value & " - " & Range("S31").Value & ", "
End If


If CheckBox25.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R41").Value & " - " & Range("S32").Value & ", "
End If


If CheckBox26.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R42").Value & " - " & Range("S33").Value & ", "
End If


If CheckBox27.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R43").Value & " - " & Range("S34").Value & ", "
End If


If CheckBox28.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R44").Value & " - " & Range("S35").Value & ", "
End If


If CheckBox29.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R45").Value & " - " & Range("S36").Value & ", "
End If


If CheckBox30.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R46").Value & " - " & Range("S37").Value & ", "
End If


If CheckBox31.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R47").Value & " - " & Range("S38").Value & ", "
End If


If CheckBox32.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R48").Value & " - " & Range("S39").Value & ", "
End If


If CheckBox33.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R49").Value & " - " & Range("S40").Value & ", "
End If


If CheckBox34.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R50").Value & " - " & Range("S41").Value & ", "
End If


If CheckBox35.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R42").Value & " - " & Range("S42").Value & ", "
End If


If CheckBox36.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R43").Value & " - " & Range("S43").Value & ", "
End If


If CheckBox37.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R44").Value & " - " & Range("S44").Value & ", "
End If


If CheckBox38.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R45").Value & " - " & Range("S45").Value & ", "
End If


If CheckBox39.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R46").Value & " - " & Range("S46").Value & ", "
End If


If CheckBox40.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R47").Value & " - " & Range("S47").Value & ", "
End If
End Sub
 
Upvote 0
Assuming you want to double click on Column A and you want the results entered in column A
And assuming you want to start this on row 28

Try this:
When you double click on column A the script will enter the results into column A if the cell in column A is empty

If the cell in column A already has some value the script will remove that value.
Since you mentioned:
instead of using checkbox, doubleclick a cell to add, then another cell to remove?

See when you say double click a cell I really do not know what cell you want to double click on so I guessed at a cell in column A

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
<strike>
</strike>​
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  8/30/2019  12:02:23 AM  EDT
If Target.Column = 1 And Target.Row > 17 Then
Cancel = True
With Target
    Select Case Target.Value
        Case ""
            .Value = Target.Offset(, 17).Value & " _ " & Target.Offset(, 18).Value & " , "
        Case Is <> ""
            .Value = ""
    End Select
End With
End If
End Sub
 
Upvote 0
I apologize for wording in my request.

I would like to know if there is an easier way to get text from 2 columns (one row at a time) added to 2 different text boxes, and reset if needed.

Currently i have 2 check boxes using the cumbersome code above to do the job. i hosted a pic.
30zbDzB

Code: 
[IMG]https://imgur.com/a/30zbDzB[/IMG]https://imgur.com/RBMLQ6O
 
Upvote 0
Your subject title says:
Hide lines in Excel

But then you said add values to a Textbox using Double click.
instead of using checkbox, doubleclick a cell to add, then another cell to remove?

OK Double click what cell??
Then Double click what cell to remove?

Give me a exact example.

Say something like double click A1 to add values to Textbox and double click B1 to clear.
​
 
Last edited:
Upvote 0
My Aswer Is This said:
Your subject title says:
Hide lines in Excel

But then you said add values to a Textbox using Double click.
instead of using checkbox, doubleclick a cell to add, then another cell to remove?

OK Double click what cell??
Then Double click what cell to remove?

Give me a exact example.

Say something like double click A1 to add values to Textbox and double click B1 to clear.
​
Click to expand...

I will start a new thread. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Combine input box (multiple criteria)
Replies
4
Views
149
gomes123
G
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
480
Joe4
Joe4
edge37
  • Solved
Toggle hide/unhide columns without affecting tables or text boxes on top
Replies
4
Views
243
Peter_SSs
Peter_SSs
I
  • Question Question
VBA to detect hidden rows. then Re-hide
Replies
7
Views
208
Skyybot
Skyybot
O
  • Question Question
Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.
Replies
2
Views
129
OaklandJim
O

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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