Hide & Show Rows with Drop Down Menu

Question

Afternoon everyone.

I have a new project I am working on and am wondering if this is possible. 

(Link to Project: https://skydrive.live.com/redir.aspx?cid=0eca664f63bb3979&resid=ECA664F63BB3979!505&parid=ECA664F63BB3979!504&authkey=!AENwRguOigJUKtk)

I am trying to find a way to use a drop down menu to add or remove (or hide and unhide) rows of cells depending on the number selected in the dropdown menu.

Bassicly, I, at the moment have 20 employees listed in this schedule. I want the user to be able to add or remove the amount of rows to allow them to insert or remove unused spaces that contain empty employees.

I have noticed that when a user has any kind of customization that they can do workbooks, are more likely to enjoy using it, and keep using it.

Answer 1

I am having a few hiccups with the excel the workbook.

 If I try and enter any information into the cells, I get a Run-time error '13': Type mismach. The Debug shows

'Get the selected number of employees again.    g_EmployeeNumber = CInt(Target.Value)

as highlighted.

 

Also, when I try to add more cells to reach the desired limit of 50, I get an error asking me to delete the last column and row of cells to prevent the loss of date.

 

Thanks for the help thus-far. Thought this thread was D.O.A. lol

OK~

I'm sorry for that error.

Changed code to refer:

Private Sub Worksheet_Change(ByVal Target As Range)

    '/* If user changes the number of employees. */

    If Target.Address = strCellAddressOfEmployeeNumber Then

        Dim intCountEmployee As Integer

        'Get the current selected number of employees.

        intCountEmployee = CInt(Target.Value)

        '/* If the current selected number of employees is not the same as before selecting. */

        If intCountEmployee <> CInt(g_EmployeeNumber) Then

            Dim dblRows As Double

            'Count rows which contained numbers in the ID column.

            dblRows = WorksheetFunction.Count(Range("A:A"))

            Dim lngHiddenFrom As Long

            Dim lngHiddenTo As Long

            '/* To obtain the range of hidden rows. */

            lngHiddenFrom = intCountEmployee + 6 + 1

            lngHiddenTo = dblRows + 6

            'Disable screen updating while hiding.

            Application.ScreenUpdating = False

            'Force to show all hidden rows.

            Rows("1" & ":" & CStr(lngHiddenTo)).Hidden = False

            '/* If the current selected number of employees is smaller than the total number of employees. */

            If intCountEmployee < dblRows Then

                Rows(CStr(lngHiddenFrom) & ":" & CStr(lngHiddenTo)).Hidden = True

            End If

            'Enable screen updating after hiding.

            Application.ScreenUpdating = True

        End If

        'Get the selected number of employees again.

        g_EmployeeNumber = Target.Value

    End If

End Sub

For your second question:

Excel will prevent data adding when there are too many merged areas and the amount of cells is varied in each area, or there might be characters such as spaces across the rows or columns.

My approach is to let all hidden rows and columns displayed, and then cancel the merged range from address "A27:I27" to the end, so that you can add data!

Here a new document has been updated, please link to this page:

https://skydrive.live.com/redir.aspx?cid=7a95b067c8b7fae6&resid=7A95B067C8B7FAE6!133&parid=7A95B067C8B7FAE6!111&authkey=!AIOuP8906u3ZAe4

If you have any other questions, welcome to tell me.

Answer 2

I am having a few hiccups with the excel the workbook.

 If I try and enter any information into the cells, I get a Run-time error '13': Type mismach. The Debug shows

'Get the selected number of employees again.    g_EmployeeNumber = CInt(Target.Value)

as highlighted.

Also, when I try to add more cells to reach the desired limit of 50, I get an error asking me to delete the last column and row of cells to prevent the loss of date.

Thanks for the help thus-far. Thought this thread was D.O.A. lol

Answer 3

Hello Matlock,

Code to refer:

In Worksheet 'Creator':

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    '/* If user changes the number of employees. */

    If Target.Address = strCellAddressOfEmployeeNumber Then

        Dim intCountEmployee As Integer

        'Get the current selected number of employees.

        intCountEmployee = CInt(Target.Value)

        '/* If the current selected number of employees is not the same as before selecting. */

        If intCountEmployee <> CInt(g_EmployeeNumber) Then

            Dim dblRows As Double

            'Count rows which contained numbers in the ID column.

            dblRows = WorksheetFunction.Count(Range("A:A"))

            Dim lngHiddenFrom As Long

            Dim lngHiddenTo As Long

            '/* To obtain the range of hidden rows. */

            lngHiddenFrom = intCountEmployee + 6 + 1

            lngHiddenTo = dblRows + 6

            'Disable screen updating while hiding.

            Application.ScreenUpdating = False

            'Force to show all hidden rows.

            Rows("1" & ":" & CStr(lngHiddenTo)).Hidden = False

            '/* If the current selected number of employees is smaller than the total number of employees. */

            If intCountEmployee < dblRows Then

                Rows(CStr(lngHiddenFrom) & ":" & CStr(lngHiddenTo)).Hidden = True

            End If

            'Enable screen updating after hiding.

            Application.ScreenUpdating = True

        End If

    End If

    'Get the selected number of employees again.

    g_EmployeeNumber = CInt(Target.Value)

End Sub

In a Module:

Option Explicit

Global g_EmployeeNumber As String

Public Const strCellAddressOfEmployeeNumber = "$I$2"

In ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()

    On Error GoTo errLine

    'Save the original value of selected number of employees.

    g_EmployeeNumber = Sheets("Creator").Range(strCellAddressOfEmployeeNumber).Value

    Exit Sub

errLine:

    MsgBox "Failed to initialize!", vbExclamation, "Error"

End Sub

If you have any questions, please download the Excel document that I modified from the following link:

https://skydrive.live.com/redir.aspx?cid=7a95b067c8b7fae6&resid=7A95B067C8B7FAE6!130&parid=7A95B067C8B7FAE6!111&authkey=!AK7f9H5Fe_cctKw

In the above Excel document(macro-enabled), you could click the drop down menu to select a number of employees from the list, and then you will see the effect you want.