Input box for excel tab

[vanessa h]

I have a workbook with many worksheets in it.
I got a macro written in my earlier post for deleting the rows in it
Now I want to make some modifications in it. When I run the macro, i
should prompt me for a input box saying "Please select a worksheet"
And after user inputs the worksheet name (excel tab name), the macr
should run on that perticular sheet only

 

[Guest]

Vanessa,

I don't know what your previous post said, but you might try this macro:

Option Explicit

Sub main()
Dim mSheet As String
mSheet = temp
If mSheet = "" Then
MsgBox ("invalid sheet entered")
Else
Sheets(mSheet).Rows("1:65536").Delete
End If

End Sub

Function temp() As String
Dim mSheet As String
Dim tSheet As Worksheet
mSheet = InputBox("enter sheet name:")
For Each tSheet In Sheets
If UCase(tSheet.Name) = UCase(mSheet) Then
temp = mSheet
Exit Function
End If
Next tSheet
End Function

I'd probably add a "Are you sure?" question in there as well.

Art

 

[vanessa h]

Hi Art,

Thank you very much for your help.

Here is the macro which I got in my earlier post.

Sub test()

Dim iRow As Long
Dim i As Long
Dim iRng As range

iRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iRow

If Application.CountIf(range("A" & i & ":A" & iRow), Cells(i, "A"))

1 Then

If iRng Is Nothing Then
Set iRng = Rows(i)
Else
Set iRng = Union(iRng, Rows(i))
End If

End If

Next n

If Not iRng Is Nothing Then iRng.Delete

End Sub

As you can see, the above macro will delet the duplicate rows in the
excel sheet.

Now I want to modify the macro so as it will prompt me for inputting
the worksheet (excel tab) name, and will delet the rows only from that
sheet and not from any other sheet.

For eg. If I have sheets as Sheet 1, Sheet 2, Sheet 3. After running
the macro, it should ask me for the sheet name. If I input the sheet
name as 'Sheet 3' then it should delete the row (refer to above macro)
from 'Sheet 3' only and not from any other sheet.

Can you help me for this?

Thanks very much in advance.

Vanessa

 

[Guest]

Vanessa,

Try this:
Sub test()

Dim iRow As Long
Dim i As Long
Dim iRng As Range
Dim mSheet As String
Dim tSheet As Worksheet
Dim mSuccess As Boolean
Dim msgResp As Integer

mSheet = InputBox("Please Enter Sheet Name")
If mSheet = "" Then Exit Sub

mSuccess = False
For Each tSheet In Sheets
If UCase(tSheet.Name) = UCase(mSheet) Then
mSuccess = True
Exit For
End If
Next tSheet

If Not mSuccess Then
MsgBox (mSheet & " is not one of the current worksheets")
Exit Sub
End If

msgResp = MsgBox("Are you sure that you want to delete the duplicate rows
from " & mSheet & "?", vbYesNo)
If msgResp = vbNo Then Exit Sub

Sheets(mSheet).Select
iRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iRow
If Application.CountIf(Range("A" & i & ":A" & iRow), Cells(i, "A")) > 1
Then
If iRng Is Nothing Then
Set iRng = Rows(i)
Else
Set iRng = Union(iRng, Rows(i))
End If
End If
Next i

If Not iRng Is Nothing Then iRng.Delete

End Sub


Art

 

[Dave Peterson]

Instead of asking for the sheet name (and having to check for typos), why not
just give them a little prompt saying that the activesheet will be cleaned up?

dim resp as long
resp = msgbox(prompt:=activesheet.name & " will be cleaned up, ok?", _
buttons:=vbyesno)

if resp = vbno then
exit sub
end if

'rest of your macro.

======
Personally, I'd just make sure that the users know that the macro works against
the activesheet. Seems pretty reasonable to me--Data|Sort doesn't prompt you
(and almost everything else, too).