Excel Hide horizontal and vertical scoll bars Ease of Use Easy Version tested with 2000, 2003 Submitted by: Description: This entry shows how to to remove the horizontal and vertical scroll bars. Discussion: There may be times where you do not want the scroll bars to show up on your worksheet, making it less likely that users will move around too much. This code removes those scroll bars and replaces them when the user moves to another workbook.
My horizontal scrollbar kept hiding after I'd updated to Excel 2016 for the MAC. I found this post about how to show and hide it. This IS the correct answer, but upon opening my settings, and as I suspected, I had the scrollbar already set to 'Always'.
I recently had an issue with a ListBox on a UserForm. It was an issue I wasn’t expecting. I thought I would share the solution I came up with. The problem I wanted to use a ListBox on a UserForm to show text. But the text could be very short or very long.
I wouldn’t know the size of the text in advance. I wanted the ListBox to be a sensible size. ListBox cuts off text I decided scrollbars were the way to go. Setting up scrollbars is easy, I just needed to make the column width wider than the width of the ListBox. But, this was still not right. As you can see in the image above, the text has still been cut off. I needed to know how wide the text was, to set the scrollbar to be the right size.
How could I find out the width of the text? I couldn’t just count how many characters there were in each line of the ListBox, because unless I was using a monospaced font, where all the characters are the same size, the width of each character will vary. For example, “W” is much wider than “i”. They are both one character, but they do not have the same width. Having a complex calculation to estimate the width based on the total width of individual characters seemed way too complex. I knew there must be a better way. The solution The solution took a bit of thinking.
I created a TextBox on the UserForm. I Set the TextBox to be Visible = False and AutoSize = True.
This makes an invisible TextBox which expands to fit the size of the text within it. I created a variable to hold the maximum width of the TextBox.
I looped through every line of text in the ListBox and entered the value into the TextBox. If the width of the TextBox becomes wider than the variable then the variable is updated.
Once the loop as finished, the variable holds the necessary size of the TextBox. I set the width of the ListBox to be equal to the variable.
Here are the ListBox and TextBox I created. Here is the code I used: Private Sub UserFormActivate 'Hack to get the scroll bars to work Dim i As Integer Dim scrollBarWidth As Double For i = 0 To myListBox.ListCount - 1 myTextBox.Value = myListBox.List(i) If myTextBox.Width scrollBarWidth Then scrollBarWidth = myTextBox.Width Next i myListBox.ColumnWidths = scrollBarWidth End Sub Here is the final result.