PDA

View Full Version : [SOLVED] Input Box problems



stapuff
09-15-2005, 02:27 PM
I borrowed this code from Cyberdude post. I can not get this to work for me at all. I selected range(s) by clicking, typing, etc. One cell, 2, 10, 100 and nothing is working. I continue to get the MsgBox "RG is nothing"

What am I doing wrong?

Thanks,

Kurt:banghead: :banghead:



Sub TestInputBox()
Dim RG As Range
On Error Resume Next
Set RG = Application.InputBox("Please choose the range", Type:=8)
On Error Goto 0
If RG Is Nothing _
Then MsgBox "RG is nothing" _
Else RG.Select
End Sub

malik641
09-15-2005, 02:46 PM
Hey kurt, just so you know I placed your VB code with VB Tags :thumb

Just to let you know.

austenr
09-15-2005, 02:48 PM
Works fine for me. What version of excel?

malik641
09-15-2005, 02:52 PM
And the code works fine for me. What are you inputting in the input box (exactly)?

stapuff
09-15-2005, 02:53 PM
malik -

Thanks. Sorry for that.

Austenr - 2000.

stapuff
09-15-2005, 02:54 PM
I am selecting A47:D49. This appears in the box as it should, but showing as nothing in the code.

malik641
09-15-2005, 03:16 PM
2 things:
1: Is that A47:B49?
2: Comment block the On Error Resume Next code AND the On Error GoTo 0, maybe when you enter this information the macro will break and show you an error (if there is one).

See if that works

stapuff
09-15-2005, 03:22 PM
malik -

1 - Yes, you are correct
2 - Run Time Error '424'

malik641
09-15-2005, 03:28 PM
Well Run-Time Error 424 means there is an object required. When it gives you the error, what line does it highlight in the code??

stapuff
09-15-2005, 03:31 PM
malik -

Code bombs out on the following:


Set RG = Application.InputBox("Please choose the range", Type:=8)

malik641
09-15-2005, 03:58 PM
Still looking good (I'm even double checking in a VBA book I have). The type is correct (Type 8 = Cell reference, as a Range object), and you defined RG as a Range.... :dunno

Can you post a workbook?

stapuff
09-15-2005, 04:15 PM
malik -

This is the total code I am running:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Select Case Target.Column
Case 1
If ActiveCell.Value = "PUR ORD" Then
ActiveCell.Offset(1, 0).Select
Call emailme
End If
End Select
End Sub

Public Sub emailme()
Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim vaRecipient As Variant
Dim rnBody As Range
Dim Data As DataObject
Const stSubject As String = "P.O. Issues"
Const stMsg As String = "Do you have an update on the following:"
Const stPrompt As String = "Please select the range:"
vaRecipient = VBA.Array("who@cares.com")
' On Error Resume Next
Set rnBody = Application.InputBox("Please choose the range", Type:=8).Value
' On Error GoTo 0
If rnBody Is Nothing _
Then MsgBox "RNG is nothing" _
Else rnBody.Select
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
Set noDocument = noDatabase.CreateDocument
rnBody.Copy
Set Data = New DataObject
Data.GetFromClipboard
With noDocument
.Form = "Memo"
.SendTo = vaRecipient
.Subject = stSubject
.Body = Data.GetText & " " & stMsg
.SaveMessageOnSend = True
End With
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
'Activate Excel for the user.
AppActivate "Microsoft Excel"
Application.CutCopyMode = False
MsgBox "The e-mail has successfully been created and distributed.", vbInformation
End With
End Sub



I have stepped through this code now about 1000 times and everything but the range select works. I can not for the life of me understand why either. Out of 100 different posts I have ready on the subject - I still can not come up with the reason why "mine" is failing.

The code I posted originally was my last ditch effort to remove the e-mailing portion of the code just to see if things would work on there own. (Not)

Thanks,

Kurt

malik641
09-15-2005, 04:30 PM
Kurt,

Just take off that .Value at the end of it in your code and that should fix it.

From:

Set rnBody = Application.InputBox("Please choose the range", Type:=8).Value


To:

S
et rnBody = Application.InputBox("Please choose the range", Type:=8)


HTH

stapuff
09-15-2005, 04:35 PM
malik -

Didn't help. Still getting the run time 424 error. I put it on to see if it would help. Is there a reference that I need to set?

Grasping at straws right about now.

Thanks,

Kurt

mdmackillop
09-15-2005, 04:49 PM
Hi Kurt,
What references do you have. I'm not getting an error at the problem line once Value is removed, but I'm missing something at the CreateObject line.
Regards
MD

malik641
09-15-2005, 04:51 PM
Didn't help. Still getting the run time 424 error. I put it on to see if it would help. Is there a reference that I need to set?
KurtThe only References I have checked are the essentials:

-Visual Basic for Applications
-Microsoft Excel 9.0 Object Library
-OLE Automation
-Microsoft Office 9.0 Object Library


Check to make sure you have these checked off in the VB Editor under Tools-->References...

And if you have them checked off then you REALLY have me stooped :think:

stapuff
09-15-2005, 05:05 PM
MD -

I have the same ref's checked as malik listed.

I am not sure why your are having an issue with CreateObject. Once I step over the error line - everything works perfectly.:dunno

mdmackillop
09-15-2005, 05:20 PM
Is Notes related to a different programme. I can create other objects, but not Note.NoteSession.

stapuff
09-15-2005, 05:22 PM
MD -

Notes is Lotus Notes. That who I have for e-mailing.

Kurt

stapuff
09-15-2005, 05:33 PM
MD -

I was able to get the code to run through without error.

One snag. Only 1 cell can be put into the input box. I can not select A1:A5 or A3:D5. This will bomb it out.

What can I do or change to get a range?

Thanks,

Kurt

malik641
09-15-2005, 05:59 PM
But I'm missing something at the CreateObject line.Yeah me too...:dunno

You can't post like a dummie workbook or something?? It would be much easier to work with what you're actually dealing with hands on.