Problems with Application.Inputbox

[Hernandez, Roberto]

Regards:

I´ve been working in the code below to 'automatize' a printing process.
The problem I have is that I can't select a range, but only one cell!
It works terrific in other workbooks, but fails in this.

If I try it selecting a range (more than one cell), I get the message error
"An object is required."

Can anybody help, please.

Thanks in advance for your support.

'****************************************************************
Sub imprimir_seleccionando()

Dim seleccion As Range

On Error Resume Next
Worksheets("Diciembre").Select

Application.ScreenUpdating = True

Set seleccion = Application.InputBox( _
Prompt:="Select the employee IDs", _
Title:="Invoices to print", _
Default:=("$B$7"), _
Type:=8) '8 type is suposed to catch a range, doesn´t it?

If seleccion Is Nothing Then
MsgBox "It doesn´t work..."
Exit Sub
Else
MsgBox "It works!!"
Worksheets("imprimir").Select
For Each c In seleccion
Range("$B$8").Value = c.Value
ActiveSheet.PrintOut
Next c
MsgBox "Done"
End If

End Sub
'**************************************************************************

 

[Guest]

That code works just fine for me. I would declare c as range though. By any
chance do you have on change, on selection change or before print code
anywhere? If so disable events at the start and procedure and re-enable at
the end...

 

[Hernandez, Roberto]

Thank you so very much for your time and support.

This code is in a 'normal' module, and absolutely there is no more code in
whole book.
I belive you when you say it works for you, because works for me in other
books.
Anyhow, i'll keep trying...

Maybe re-starting from a blank book.

Thanks again!!

 

[Guest]

What line is it erroring out on...

 

[Hernandez, Roberto]

What line is it erroring out on...

'****************************************************************
...
...
...

Application.ScreenUpdating = True

Set seleccion = Application.InputBox( _ '<==== Right Here: "An object is
requiered", when selecting more than one cell.
Prompt:="Select the employee IDs", _
Title:="Invoices to print", _
Default:=("$B$7"), _
Type:=8) '8 type is suposed to catch a range, doesn´t it?
...
...

Worksheet "Diciembre" used to have conditional formatting, but it's 'clean'
now.

What could it be?

 

[Guest]

Avoid the select statements. It is hard for me to tell but give this a try...

Sub imprimir_seleccionando()

Dim seleccion As Range
dim wksDiciembre as worksheet
dim wksImprimir as worksheet

set wksDiciembre = Worksheets("Diciembre")
set wksImprimir = Worksheets("imprimir")

wksDiciembre .Select
Application.ScreenUpdating = True

Set seleccion = Application.InputBox( _
Prompt:="Select the employee IDs", _
Title:="Invoices to print", _
Default:=("$B$7"), _
Type:=8) '8 type is suposed to catch a range, doesn´t it?

If not seleccion Is Nothing Then
MsgBox "It works!!"
For Each c In seleccion
wksImprimir.Range("$B$8").Value = c.Value
wksImprimir.PrintOut
Next c
MsgBox "Done"
Else
MsgBox "It doesn´t work..."
End If

End Sub

There is nothing in your old code that should clear any formatting or such.

 

[Hernandez, Roberto]

Thank you so much Jim!
I ment I clear by hand the conditional formatting so I could solve the
problem.
Anyway, your code works fine.

I'll forget this misterious passage of my 'Excel-VBA' life...
And use your solution.

Regards.

 

[Guest]

Never forget. You figured out a perfectly good way how not to do something.
That is very valuable information. At least as valuable as how to do it right.