Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WHY OH WHY DOESN'T THIS WORK!!!!!
I have this bit of code:
Public InvoiceRange As Range Sub SelectDataRow() 'finds the last used row in the sheet that the user presently has active Dim LastUsedRow As Integer LastUsedRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row 'get the user to select a row of data for moving to the Log Set InvoiceRange = Application.InputBox("Either: " & vbCrLf & _ "1.Confirm the present selection by hitting OK" & vbCrLf & _ "2.Select a cell in another row and hit OK" & vbCrLf & _ "3.Hit CANCEL to stop the macro here", _ "RELEVANT PAYMENT INFORMATION", "A" & _ LastUsedRow & ":L" & LastUsedRow, , , , , 8) 'if nothing has been selected on the activesheet then this is flagged to the user If InvoiceRange Is Nothing Then MsgBox "No cells on the ACTIVESHEET have been selected" End If End Sub It doesn't seem to always work!! When it hits the "Set InvoiceRange =..." line it sometimes works, and sometimes fails. The error is Object Required. I realise that if nothing is selected when the Inputbox method is used then this is the correct error but even when I select a cell or range on the activesheet the error can occur. SOMEBODY PLEASE HELP AS I'M REALLY BANGING MY HEAD AGAINST A BRICK WALL WITH THIS ONE!!! Any help greatly appreciated, Jason. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WHY OH WHY DOESN'T THIS WORK!!!!!
Use On Error Resume Next Before the Set command. When you Click Cancel,
then the Input Box returns false, which is not an object. Then your check to see if InputRange is nothing should work correctly Charles Chickering WhytheQ wrote: I have this bit of code: Public InvoiceRange As Range Sub SelectDataRow() 'finds the last used row in the sheet that the user presently has active Dim LastUsedRow As Integer LastUsedRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row 'get the user to select a row of data for moving to the Log Set InvoiceRange = Application.InputBox("Either: " & vbCrLf & _ "1.Confirm the present selection by hitting OK" & vbCrLf & _ "2.Select a cell in another row and hit OK" & vbCrLf & _ "3.Hit CANCEL to stop the macro here", _ "RELEVANT PAYMENT INFORMATION", "A" & _ LastUsedRow & ":L" & LastUsedRow, , , , , 8) 'if nothing has been selected on the activesheet then this is flagged to the user If InvoiceRange Is Nothing Then MsgBox "No cells on the ACTIVESHEET have been selected" End If End Sub It doesn't seem to always work!! When it hits the "Set InvoiceRange =..." line it sometimes works, and sometimes fails. The error is Object Required. I realise that if nothing is selected when the Inputbox method is used then this is the correct error but even when I select a cell or range on the activesheet the error can occur. SOMEBODY PLEASE HELP AS I'M REALLY BANGING MY HEAD AGAINST A BRICK WALL WITH THIS ONE!!! Any help greatly appreciated, Jason. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WHY OH WHY DOESN'T THIS WORK!!!!!
Just to add an example:
Dim InvoiceRange as range .... set InvoiceRange = nothing on error resume next set invoicerange = application.inputbox(Prompt:="long string", type:=8) on error goto 0 if invoicerange is nothing then 'cancel else 'not cancel end if WhytheQ wrote: I have this bit of code: Public InvoiceRange As Range Sub SelectDataRow() 'finds the last used row in the sheet that the user presently has active Dim LastUsedRow As Integer LastUsedRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row 'get the user to select a row of data for moving to the Log Set InvoiceRange = Application.InputBox("Either: " & vbCrLf & _ "1.Confirm the present selection by hitting OK" & vbCrLf & _ "2.Select a cell in another row and hit OK" & vbCrLf & _ "3.Hit CANCEL to stop the macro here", _ "RELEVANT PAYMENT INFORMATION", "A" & _ LastUsedRow & ":L" & LastUsedRow, , , , , 8) 'if nothing has been selected on the activesheet then this is flagged to the user If InvoiceRange Is Nothing Then MsgBox "No cells on the ACTIVESHEET have been selected" End If End Sub It doesn't seem to always work!! When it hits the "Set InvoiceRange =..." line it sometimes works, and sometimes fails. The error is Object Required. I realise that if nothing is selected when the Inputbox method is used then this is the correct error but even when I select a cell or range on the activesheet the error can occur. SOMEBODY PLEASE HELP AS I'M REALLY BANGING MY HEAD AGAINST A BRICK WALL WITH THIS ONE!!! Any help greatly appreciated, Jason. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WHY OH WHY DOESN'T THIS WORK!!!!!
Thanks very much lads.
Regards Jason. Dave Peterson wrote: Just to add an example: Dim InvoiceRange as range ... set InvoiceRange = nothing on error resume next set invoicerange = application.inputbox(Prompt:="long string", type:=8) on error goto 0 if invoicerange is nothing then 'cancel else 'not cancel end if WhytheQ wrote: I have this bit of code: Public InvoiceRange As Range Sub SelectDataRow() 'finds the last used row in the sheet that the user presently has active Dim LastUsedRow As Integer LastUsedRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row 'get the user to select a row of data for moving to the Log Set InvoiceRange = Application.InputBox("Either: " & vbCrLf & _ "1.Confirm the present selection by hitting OK" & vbCrLf & _ "2.Select a cell in another row and hit OK" & vbCrLf & _ "3.Hit CANCEL to stop the macro here", _ "RELEVANT PAYMENT INFORMATION", "A" & _ LastUsedRow & ":L" & LastUsedRow, , , , , 8) 'if nothing has been selected on the activesheet then this is flagged to the user If InvoiceRange Is Nothing Then MsgBox "No cells on the ACTIVESHEET have been selected" End If End Sub It doesn't seem to always work!! When it hits the "Set InvoiceRange =..." line it sometimes works, and sometimes fails. The error is Object Required. I realise that if nothing is selected when the Inputbox method is used then this is the correct error but even when I select a cell or range on the activesheet the error can occur. SOMEBODY PLEASE HELP AS I'M REALLY BANGING MY HEAD AGAINST A BRICK WALL WITH THIS ONE!!! Any help greatly appreciated, Jason. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) |