Command Button to run Macro

[CrashMatRob]

Hi,
I have a macro named "RemoveEmployee" which works the way I want.
What I would like to do is, create a user form with a command button which when clicked will run the above macro, 1 button per employee, is this possible?

A better solution would be, on a user form select an employee from from a list on sheet1 B4:B23 and then run the above macro.

Many Thanks Rob

 

[hippiehacker]

and what do you do with the username from the list?

 

[CrashMatRob]

The user name would also be removed.

This is for a employee holiday calender, where there is a summary sheet (sheet1) and sheets for each month i.e sheet2=jan sheet3=feb and so on.
the macro I have removes the employee and all data (holidays booked and sick day leave)

Rob

 

[hippiehacker]

anyway create a userform with a combobox and button

Private Sub UserForm_Initialize()
'fill combobox with users
Me.ComboBox1.Value = " - Select User - "
Me.ComboBox1.List = Worksheets("Sheet2").Range("B4:B23").Value
End Sub

Private Sub CommandButton1_Click()
'button to call macro
Call macroname
End Sub

 

[CrashMatRob]

Many thanks
the code works just as it should.

I,ve just got to work on the marco now, as whenever I click the command button it only removes all the data from the first name in the list, regardless of any other name which is selected.

Rob

 

[hippiehacker]

its a combobox you can only select one name in your worksheet in which column is the name and can the name be several times found?

 

[CrashMatRob]

Thank you for your help,

The name is on the Sheet1(summary sheet) in B4, with days holiday in C4, and days bought forward in D4
B5 next name, B6 next name upto B23

Sheet2(Jan) has holidays booked in a calender format with the name from sheet1 B4 repeated in C9(=Summary!B4)
C10(=Summary!B5) upto C27(=Summary!B23)

The code below, not mine but found on the net and edited a little, works to remove name and all data from all 13 sheets
but only for the first employee sheet1(B4), sheet2(D8:AS8)
Therefore which ever name I choose in the comobox and click the command button to run code will only do the above.

I need to be able to select a name from the comobox, run the code which would remove all data relevant to the selection.
Hope this makes sense.

Thx's for your time
Rob

Sub RemoveEmployee1()

' RemoveEmployee1 Macro
' Delete all Data for Employee

    Dim ws As Worksheet
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="Test"
    For Each ws In ActiveWorkbook.Sheets
         'False to extend the current selection to include
         ' any previously selected objects and the specified object
        ws.Select False
            Range("D8:AS8").Select
    Selection.ClearContents
    Range("D8").Select
        Sheets("Summary").Select
    Range("B4:D4").Select
    Range("D4").Activate
    Selection.ClearContents
    Range("B4").Select
    Next ws
    ActiveSheet.Protect Password:="Test"
    Application.ScreenUpdating = True
End Sub