Need help with a specific macro. Maybe better to use VBA?

[figuare9]

I'm playing with an idea of setting something up for a touch screen. However, I'm a bit of a beginner and require some assistance.

In the picture you can see a list of employees, and a couple buttons to the right. I'm trying to achieve a result that would work in the following way.

1. Touch "Remove Employee" and have a popup window appear. "Please select the employee you want to remove." (Okay)
2. Select the employee and have a popup window appear. "Are you sure you want to remove this employee?" (Yes/No)
3. After employee is removed, I need everything to automatically move to the empty spot. So if I removed Roberto, James and everyone undernearth and to the right would move up. The employees are organized by seniority. From top left being the oldest, and bottom right being the newest. Going down in each row. Robert - Jesse, then Terry through Paul, etc. etc.

Should I do this with a macro? Or VBA? I've never worked with active selections causing an event before, so I got lost right in Step 1 with this project.. haha. Any help would be amazing.. I'm kinda stuck here. I'm also only focusing on deleting an employee right now. The add can be next. haha. But I should mention that each employee will have a worksheet linked to them. So for instance, when I click Robert, it will bring me into a sheet for just him. So when they get deleted, I'd like it to also delete Robert's personal information sheet as well, and so forth.

 

[rpaulson]

figuare9,


try this on a copy of you file.

not too difficult,

I would create a new worksheet named "Employees", with the Names going down starting in cell A2

then run the Fill_it code below one time.
this will Popluate the names on your sheet ("Sheet1") the first time.

the assign the Delete_employee macro to your button.

here are the macros.

Sub Delete_employee()
On Error Resume Next
Set myRange = Application.InputBox(Prompt:="Please click on the Employee you want to remove", _
Title:="Select Row", Type:=8)
r1 = myRange.Row
c1 = myRange.Column
If myRange Is Nothing Then Exit Sub
emp = Cells(r1, c1).Value

x = InputBox("Type YES to delete  - " & emp)
If UCase(x) <> "YES" Then
    MsgBox "Employee NOT deleted"
    Exit Sub
End If

Dim es As Worksheet
Set es = Worksheets("Employees")

r = Application.Match(emp, es.Range("A:A"), 0)
MsgBox r
es.Rows(r).Delete Shift:=xlUp

Call Fill_it

End Sub

Sub Fill_it()

Dim es As Worksheet, ws As Worksheet
Set es = Worksheets("Employees")
Set ws = Worksheets("Sheet1")

wr = 2
For c = 3 To 23 Step 5
For r = 8 To 33 Step 5
ws.Cells(r, c) = es.Cells(wr, "A")
wr = wr + 1
Next r
Next c

End Sub

enjoy,

Ross

 

[JLGWhiz]

Just for information
One of the first things in automation is to design the database to minimize the probability of error. In the case of an employee roster, there are many people with the same names, i.e. John Smith, Sally Brown, etc. Some of them can be made singular by adding an initial or second name, but the easy and most efficient way is to use an employee ID number in an adjacent column in the roster and to also use that ID number on any related data files for the employee. That unique employee number will minimize the probability of mixing data between emplyees with similar or same names.

BTW, VBA is a programming language and is used to write macros. So if you are dealing with macros in Excel, they are either VBA or VB Script programming language. Both are accepted by Excel for creating macro procedures. A macro is nothing more than a short program that generally accomlishes only one or two repetitive tasks. Multiple macros or complex macros that accomplish a series of events and produce output of products such as reports, charts and text are generally referred to as programs, which can also be written in VBA or VB Script.

VBA can be used to program touch screen in the same manner as regular mouse and keyboard operation equipment. The touch event is the same as a click event with respect to buttons and shapes.

 

[figuare9]

The fill_it didn't quite work. Ended up offsetting everything. it correctly filled my first row of 6, then ended up skipping a bunch. Looking into it now.

 

[rpaulson]

your project intrigues me:

I uploaded my working file here:
http://www.computermaninc.net/Employee manager.xlsm

hth,

Ross

 

[figuare9]

Okay I got it. You had the offsets for 2 columns and I only had one. Sweet. This is working well so far. I love it! Great job. Thanks Ross!! Now how about an add employee?? :D

 

[ddhuggi]

I would recommend using VBA with command buttons. There are several threads that can assist you on inputting and coding Command buttons.

This would allow you to set diffent functions to each command button and well and make the interface of each button on the screen with the employees name an active call button to pull up their "tab" or sheet in your workbook.

 

[rpaulson]

code to add employees

Sub Add_employee()

Dim es as worksheet
Set es = Worksheets("Employees")

lr = es.Cells(Rows.Count, "A").End(xlUp).Row + 1
es.Cells(lr, "A") = InputBox("Enter employee number")
es.Cells(lr, "B") = InputBox("Enter employee First Name")
es.Cells(lr, "C") = InputBox("Enter employee Last Name")
Call Fill_it

End Sub

 

[figuare9]

This is awesome. I made some small changes to it, like removing the ID # and first and last name. But it works perfectly. Thank you so much! This is great. I really appreciate the help. I'll have to show it off when I get a little farther with it. Again, I really appreciate your help. It means a lot!!!

 

[ddhuggi]

[Make sure that you give everyone a likes or thumbs up that assisted you. Rep is a counted.