This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(307.2, 46%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
I am trying to find why my macro won't work. I want cells to be cleared for a set number of tabs within the workbook based on a user-inputted range, but I can't seem to get it to work. Can someone please point out where I'm going wrong here?
Thanks,
Mike
Rather than expecting us to re-key your macro from a screen shot, please post your code as text. Thanks!
Hi John, please see code below:
Sub Clear_cells()
Dim rng As Range
Dim i As Integer
'Get range from user that they want cells cleared from witin xxxxx sheets
On Error Resume Next
Set rng = Application.InputBox( _
Title:="Cell Range Input", _
Prompt:="Select a range to clear in department sheets", _
Type:=8)
On Error GoTo 0
'Test to ensure User Did not cancel
If rng Is Nothing Then Exit Sub
'Set Variable to first cell in user's input (ensuring only 1 cell)
'Set rng = rng.Cells(1, 1)
'loop through each sheet within indexed sheet range, and clear the cells based on what range the user inputted in the InputBox
For i = Sheets("Control").Range("E1") To Sheets("Control").Range("E2")
ThisWorkbook.Worksheets(i).rng.ClearContents
Next i
End Sub
Which text is expected to be entered into Input box?
a cell range
How can one enter a Range object through a simple text InputBox? It's simply not possible and fails, but because of the "On Error Resume Next" you don't get it. That don't work
Test:
Option Explicit
Public Sub Test()
Dim rng As Range
Set rng = Application.InputBox("Test", "Test")
Debug.Print rng
End Sub
So there is no way to get a range from user input?
The "Type:=8" indicates that the type of input the user in entering is a Range object.
On Error Resume Next
Set rng = Application.InputBox( _
Title:="Cell Range Input", _
Prompt:="Select a range to clear in department sheets", _
Type:=8)
On Error GoTo 0