MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Input Box to allow a user to choose a cell to delete


Posted by Kurt on July 19, 2000 7:42 AM

Hello,

I am trying to write a routine that will allow a user to input a cell that they wantto delete. Here's the code I have so far:

Sub Delete_Cell()
Const strTaskName = "Cell to Delete"

'Dim strEnterCell As String

Worksheets("Sheet1").Select

strEnterCell = InputBox(prompt:="Enter the cell you want to delete " & _
"in R1C1 format:", _
Title:=strTaskName)
Range(TaskName).Select
Selection.ClearContents

End Sub


I keep bombing on the Range(TaskName).Select line.

Help and thanks!!!

Kurt


Posted by Ivan Moala on July 20, 0100 1:23 AM

Kurt
As an alternative to Ryans fix and the way
you have done it is to use the Application.input
method instead.
This allows you to select a Range instead of
typing in a range (more chance for errors)
See below;

Sub Delete_RangeSelect()
Dim Ans As Integer
Const Prompt = "Select a range or cell to Delete."
Const YourTitle = "Cell selection"
Const Can = "UserInput Cancelled!"

'Display the Input Box
On Error Resume Next
'Incase of Error let the following
'input Routine handle it.
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=YourTitle, _
Default:="", _
Type:=8) '8 indicates a cell reference, as a Range object

'test for cancel
If UserRange Is Nothing Then End
' Was no selection made?
If UserRange.Count = 0 Or UserRange = "" Then
MsgBox Can
End
Else
Ans = MsgBox("Delete Range: " & UserRange.Address & " ?", vbYesNo)
If Ans = vbYes Then Range(UserRange.Address).Clear
End If

End Sub

Ivan

Posted by Ryan on July 19, 0100 8:16 AM

Kurt,

Here you go. Don't know where you got the name "TaskName" from for the Range but there is not a reference to it anyplace else. Also, it might be easier for the person to insert the Range as A1 instead og R1C1 format b/c they would have to enter 1:1. Either way works. Let me know how it goes!

Ryan
Sub Delete_Cell()
Application.ScreenUpdating = False
Const strTaskName = "Cell to Delete"

Dim strEnterCell As String

Worksheets("Sheet1").Select

strEnterCell = InputBox(prompt:="Enter the cell you want to delete " & _
"in R1C1 format:", _
Title:=strTaskName)
Range(strEnterCell).ClearContents

Application.ScreenUpdating = True
End Sub

Posted by Kurt on July 19, 0100 1:59 PM

Ryan it worked great!!!

Thanks a million!!

Kurt