Display/Hide # of columns based on user input

[mrMozambique]

Hi all. This may be around somewhere in the forums, but I appear to be searching incorrect key words.

I have a range of columns (I:AQ) that a user can freely use to enter quantitative data. Each column represents a province of Vietnam. Column AR contains qualitative data (comments) that can be used to explain the quantitative data in columns I:AQ. The large majority of users will use only a few columns. However, I needed to open it to 35 columns to accommodate a small group of users that work in many provinces.

I'd like to create a combobox or validation list in a cell where users select the number of provinces they work in and then run a macro to display the number of columns the user selects. For example, if the user selects 10 provinces, columns I:R will be displayed and S:AQ are hidden.

I'm sure this is possible, but I'm only an beginner/intermediate VBA programmer. Any suggestions?

Thanks in advance.

 

[SteveO59L]

Something along the lines of

Columns("I:aq").Select
Selection.EntireColumn.Hidden = True
mynum = Application.InputBox("Enter a number")
Range("I1").Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + mynum).Select
Selection.EntireColumn.Hidden = False

 

[crook_101]

Hi,

If you take a look at this thread,

http://www.mrexcel.com/forum/showthread.php?t=493851

I have detailed how to add buttons and add VBA that should fit your needs.

Cheers,

Ian.

 

[mrMozambique]

Thanks, that worked great. Here's what I finished with. I have a minimum of five displayed.

Sub ShowColumns()

Columns("N:AQ").Select
Selection.EntireColumn.Hidden = True
mynum = Application.InputBox("How many provinces to display (min 5 / max 35)?")

If mynum < 5 Or mynum > 35 Then

Msgbox "Please select a number between 5 and 35", vbOKOnly, "Incorrect Entry"

Else

Range("M1").Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + mynum - 5).Select
Selection.EntireColumn.Hidden = False

End If

End Sub