Creating an inputbox for a vba scrip

[marcvanderpeet12]

Dear all,


I've written the following VBA text which allows me to send part of an excel file automatically by email:


Sub Send_Range()

ActiveSheet.Range("A1:B5").Select

ActiveWorkbook.EnvelopeVisible = True


With ActiveSheet.MailEnvelope

.Introduction = "Dit is een voorbeeldwerkblad."

.Item.To = "marc.van.der.peet@achmea.nl"

.Item.Subject = "Onderwerp"

.Item.Send

End With

End Sub


Thing is now that ideally I want to create pop-up boxes where I can insert information (to change the variables)


So instead of copying the range A1:B5 I want to be able to insert the range myself. Same goes for the email adress I want to sent the copy to (so I want to enter manually the email adress in an email box).


Anybody suggestions on how I can achive this?


Dear regards,


Marc

 

[Luke M]

I think you want to use the

InputBox object.

Example code:

[pre]Sub InputExample()
Dim xInput As String
xInput = InputBox("What to say?", "Hello", "Default answer")
MsgBox "You said: " & xInput
End Sub

[/pre]

 

[marcvanderpeet12]

Dear Luke,


Thanks for your reply.


I tried and it works with text fields. It does however not work when I want to copy a range. If I want to copy a range in an excel sheet and I want to sent this by mail i wanted to use the following code:


Sub Test1()

'

' Test1 Macro

' Macro recorded 30-7-2013 by Marc van der Peet


Dim xinput As Range

xinput = InputBox("Fill in your range")


ActiveSheet.Range(xinput).Select

ActiveWorkbook.EnvelopeVisible = True


With ActiveSheet.MailEnvelope

.Introduction = ("hallo")

.Item.To = ("marc.van.der.peet@achmea.nl")

.Item.Subject = "Onderwerp"

.Item.Send

End With

End Sub


Does not work. I get an error in this line: xinput = InputBox("Fill in your range")


Any suggestions?


Dear regards,


Marc

 

[Luke M]

Sorry, I did not understand the intent before. In the current setup, you appear to be wanting to give xInput as a string. In which case, change the Dim statement. The code will work as long as user gives a valid address.

Or, you can change the inputbox to be the kind where user can click on worksheet itself to select range. In which case, beginning of code becomes

[pre]

Dim xInput As Range
Set xInput = Application.InputBox(Prompt:="Please Select Range", _
Title:="Range Select", Type:=8)
xInput.Select
ActiveWorkbook.EnvelopeVisible = True

[/pre]