asked on

How do i make a button create 2 new sheets?

I want a button on excel to create 2 new pages, but they will just be hidden on the back end then will unhide and add information from the main page when i press the button? Is this possible? I know there is this code if you do a checkbox:

Private Sub CheckBox1_Click()
Sheets("Sheet1").Visible = CheckBox1.Value
End Sub

But I am having trouble doing the similar thing with a button instead. Can you help?

Serge Fournier

I decided to give it all my skills :)

There is a type variable to be able to change the number of parameters calling the function without changing all the functions call
There is a function to create the button
And finally the sub to unhide the sheet

'=== variables passed to button add to cell
Type button_add_to_range_type
    ybut As Integer
    xbut As Integer
    ysize As Integer
    xsize As Integer
    button_name_new As String '=== space not authorized
    button_description As String
    button_sub_to_call As String
    she01 As Excel.Worksheet

End Type

Sub main()

    Set she01 = ThisWorkbook.ActiveSheet
    
    '=== add button
    Dim button_add_to_range_var As button_add_to_range_type
    
    button_add_to_range_var.ybut = 2
    button_add_to_range_var.xbut = 2
    button_add_to_range_var.ysize = 1
    button_add_to_range_var.xsize = 1
    button_add_to_range_var.button_name_new = "unhide_sheet"
    button_add_to_range_var.button_description = "Unhide Sheet"
    button_add_to_range_var.button_sub_to_call = "unhide_Sheets"
    Set button_add_to_range_var.she01 = she01

    row_increment_Additionnal = button_add_to_range(button_add_to_range_var)
    
    
    
End Sub

Sub unhide_Sheets()
     Sheets("Sheet2").Visible = True
     MsgBox ("sheet unhidden")
     
End Sub


Function button_add_to_range(button_add_to_range_var As button_add_to_range_type)
    '=== button insertion
    
    Set range01 = button_add_to_range_var.she01.Range(button_add_to_range_var.she01.Cells(button_add_to_range_var.ybut, button_add_to_range_var.xbut), _
    button_add_to_range_var.she01.Cells(button_add_to_range_var.ybut + button_add_to_range_var.ysize, button_add_to_range_var.ybut + button_add_to_range_var.xsize))
    'Set cell = sshe.Cells(yy, xx)
    
    '=== delete button before creating it
    For Each button01 In button_add_to_range_var.she01.Buttons
        buttonname01 = ""
        On Error Resume Next
        buttonname01 = button01.Name
        On Error GoTo 0
        If buttonname01 <> "" Then
            If button01.Name = button_add_to_range_var.button_name_new Then
                button_add_to_range_var.she01.Buttons(button01.Name).Delete
            End If
        End If
    Next
    
    'MsgBox (sshe.Cells(yy, xx).Top & vbCrLf & rrng.Top & vbCrLf & sshe.Cells(58, 1).Top)
    
    Set button01 = button_add_to_range_var.she01.Buttons.Add(range01.Left, range01.Top, range01.Width, range01.Height)
    'Set btn = sshe.Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height)
    
    '=== Move and Size with cells
    '=== the first insertion of the button is bad, we realign it
    button01.Top = range01.Top
    button01.Left = range01.Left
    button01.Width = range01.Width
    button01.Height = range01.Height
    
    'rrng.Interior.ColorIndex = 38
    
    button01.Caption = button_add_to_range_var.button_description
    button01.OnAction = button_add_to_range_var.button_sub_to_call
    button01.Name = button_add_to_range_var.button_name_new
    
    Set addbut = button01

End Function

Open in new window

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kylie Thomas

ASKER

Thanks this will work!

Roy Cox

Pleased to help