ExcelBanter - Newbie VBA Problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Newbie VBA Problem (https://www.excelbanter.com/excel-programming/365892-newbie-vba-problem.html)

Newbie VBA Problem

I stink at Excel programming.

Anyways, I have this button which removes employees. And when clicked,
it will display this remove an employee window. Very simple, the user
types in the employee name to remove and then clicks remove and then a
confirmation screen will appear. Only problem is the confirmation
screen won't have access to the employee name which is pretty obvious.
So I would want to send the employee name to that screen, but no matter
what code I try I can't get it to work! Grrr

Here is the code I have:

This is the remove employee menu.

Code:
--------------------

Option Explicit
-----------------

Private Sub removeEmployeeRecord_Click()
confirmRemovalEmployee(employeeName.Text).Show
End Sub

--------------------

The confirmation menu to remove code

Code:
--------------------

Option Explicit
Private Sub cancel_Click()

Unload Me

End Sub

Private Sub confirm_Click(employeeName as String)

Worksheets(employeeName).Delete

End Sub

--------------------

Can someone help me here? And does anyone know of some really good VBA
Excel tutorials? Please do list them.

Thank You.

--
FCC
------------------------------------------------------------------------
FCC's Profile: http://www.excelforum.com/member.php...o&userid=35888
View this thread: http://www.excelforum.com/showthread...hreadid=557162

Newbie VBA Problem

If you're going to type in the name on the first form, then pass the entire
form and control info to the receiving routine as UserForm1!employeeName.Text

But I think a better way might be to set things up to get the information
right from the Excel sheet with the names on it? No chance of typo and the
person has to click the one to be removed first. Then the current ActiveCell
has the name and both forms can reference it.

As I understand it from your example, each employee is on a separate sheet
with their name used as the sheet name on its tab? -- In that case use
ActiveSheet vs ActiveCell. This also doesn't need a separate form, just uses
a Message Box and examines the responses.

Sub removeEmployeeRecord_Click()
Dim Reply As Variant
'*** all here goes on one line
Reply = MsgBox("Confirm delete of employee " & ActiveSheet.Name,
vbYesNoCancel, "Confirmation Required")
'**** end of the one line
If Reply < vbYes Then
Exit Sub ' just ignore for No and/or Cancel
End If
'here if they click YES
Worksheets(ActiveSheet.Name).Delete
End Sub

They'll get a warning when the .Delete line is executed. If you want to
just delete the sheet at that point without giving them another chance to
back out, use this:
Application.DisplayAlerts = False
Worksheets(ActiveSheet.Name).Delete
Application.DisplayAlerts = True

As for entry level VBA for Excel training, sorry, someone else will have to
point you to that, I haven't looked for any. Probably should, since it is an
often requested item here in these forums and others. My bad.

"FCC" wrote:

I stink at Excel programming.

Anyways, I have this button which removes employees. And when clicked,
it will display this remove an employee window. Very simple, the user
types in the employee name to remove and then clicks remove and then a
confirmation screen will appear. Only problem is the confirmation
screen won't have access to the employee name which is pretty obvious.
So I would want to send the employee name to that screen, but no matter
what code I try I can't get it to work! Grrr

Here is the code I have:

This is the remove employee menu.

Code:
--------------------

Option Explicit
-----------------

Private Sub removeEmployeeRecord_Click()
confirmRemovalEmployee(employeeName.Text).Show
End Sub

--------------------

The confirmation menu to remove code

Code:
--------------------

Option Explicit
Private Sub cancel_Click()

Unload Me

End Sub

Private Sub confirm_Click(employeeName as String)

Worksheets(employeeName).Delete

End Sub

--------------------

Can someone help me here? And does anyone know of some really good VBA
Excel tutorials? Please do list them.

Thank You.

--
FCC
------------------------------------------------------------------------
FCC's Profile: http://www.excelforum.com/member.php...o&userid=35888
View this thread: http://www.excelforum.com/showthread...hreadid=557162

Newbie VBA Problem

I see. Thanks.

While on the topic of newbie questions, can anyone tell me what is
wrong with these statements?

Code:
--------------------

Dim length As Double
length = CDbl(lengthOfSickness.Text)

--------------------

I always get Run-Time error '13': Type mismatch when I don't have any
data entered into the textbox of lengthOfSickness. I don't understand
why it keeps telling me it's a mismatch when I've used the function
CDbl to convert the text into a double.....

--
FCC
------------------------------------------------------------------------
FCC's Profile: http://www.excelforum.com/member.php...o&userid=35888
View this thread: http://www.excelforum.com/showthread...hreadid=557162

Newbie VBA Problem

Just check to see if you have something that looks like a number in that textbox
first:

Dim length As Double
If IsNumeric(lengthOfSickness.Text) Then
length = CDbl(lengthOfSickness.Text)
Else
length = 0 '???
End If

FCC wrote:

I see. Thanks.

While on the topic of newbie questions, can anyone tell me what is
wrong with these statements?

Code:
--------------------

Dim length As Double
length = CDbl(lengthOfSickness.Text)

--------------------

I always get Run-Time error '13': Type mismatch when I don't have any
data entered into the textbox of lengthOfSickness. I don't understand
why it keeps telling me it's a mismatch when I've used the function
CDbl to convert the text into a double.....

--
FCC
------------------------------------------------------------------------
FCC's Profile: http://www.excelforum.com/member.php...o&userid=35888
View this thread: http://www.excelforum.com/showthread...hreadid=557162

--

Dave Peterson

Newbie VBA Problem

Regarding the deletion of a spreadsheet, is there anyway to get rid o
Excel's automatic warning of deletion?

I want this because I made a deletion confirmation, and I don't exactl
what the user to have to press the delete button three times instead o
two

--
FC
-----------------------------------------------------------------------
FCC's Profile: http://www.excelforum.com/member.php...fo&userid=3588
View this thread: http://www.excelforum.com/showthread.php?threadid=55716

Newbie VBA Problem