Help on Input box in excel using VBA

[Prasoonj]

Hi All,

I'm a beginner in excel and I need your assistance to complete my requirement.

Scenario : I need to copy data from Sheet1 Range starts from cell name C1 and end with the cell name which user required and need to paste it in .vbs file.

Please assist me for creating INPUT BOX which may prompt,
so that the user can enter his end cell name.

Thanks in advance.

 

[shrivallabha]

Hi Prasoon,

Try like below:

Sub GetInputBox()
Dim r As Range
'\\ gives error if user doesn't select a cell
On Error Resume Next
Set r = Application.InputBox(Prompt:="Select End Cell", Title:="Cell Select", Type:=8)
On Error GoTo 0
If Not r Is Nothing Then
  MsgBox "You selected cell " & r.Address
Else
  MsgBox "Please select appropriate cell!", vbExclamation
End If
End Sub

 

[Prasoonj]

Thanks for your quick reply Shrivallabha,

Your codes help me but I need the data range the user given should be copied to .vbs file (Path "C:\Users\prasoonj\Desktop\New folder").

Please help me on this.

 

[prasaddn]

Hi Shrivallabha,

Request you to explain the various values for "type:" parameter of application.inputbox() pls.

Regards,
Prasad DN

 

[shrivallabha]

Hi Prasoon,

You had asked for InputBox part only in the post #1. If the user is selecting the range as per his requirement then there has to be some logic to writing this to file.
e.g.
If user selected range is A1:C20 then in what order the data should be written in the file?
Please provide couple of clear examples.

Hi Prasad,

Copy above code to Visual Basic Editor. Keep cursor on the word "inputbox" and then just press F1. You should be able to find out all options

 

[Prasoonj]

Hi Shrivallabha,

I will be using only one row in my template to copy datas from excel to .vbs file,
like A2:A57 or A2:A123, not like A1:C20 as you mention.

Sorry i have not clearly mentioned this is my 1st post.

I have attached a excel file which will help you better to understand my requirement.

Thank you very much for your reply and help.

 

[Debraj]

Hi Prasoonj,

Please correct me If I am wrong

a UN-NAMMED script file (VBS), which you want to open, may be in Notepad (or Visual Studio)
then in anywhere in the file, (not module / procedure) you want to PASTE the provided range's (using last cell only) > using Input box > in another application excel,

and want to save that .VBS again in script-able programming code.. irrespective of Syntax error anything..

Am I correct..!!!

 

[shrivallabha]

Hi Prasoon,

Please test below code. Before running read comments first and update to match it at your end. This is basic code so you need to handle the goof ups or error that users may do.

Sub CreateVBSFile()
Dim r As Range
Dim ff As Integer
Dim vInput As Variant
Dim strInput As String

'\\ gives error if user doesn't select a cell
On Error Resume Next
Set r = Application.InputBox(Prompt:="Select End Cell", Title:="Cell Select", Type:=8)
On Error GoTo 0

If Not r Is Nothing Then
  ff = FreeFile
  '\\ Will error if the folder path does not exist
  Open "C:\Temp\Test.vbs" For Output As #ff
  vInput = Application.Transpose(Range("C2", "C" & r.Row).Value) '// Change this to suit
  strInput = Join(vInput, vbCrLf)
  Print #ff, strInput
  Close #ff
Else
  MsgBox "Please select appropriate cell!", vbExclamation
End If

End Sub

 

[Prasoonj]

HI

Hi Prasoonj,

Please correct me If I am wrong

a UN-NAMMED script file (VBS), which you want to open, may be in Notepad (or Visual Studio)
then in anywhere in the file, (not module / procedure) you want to PASTE the provided range's (using last cell only) > using Input box > in another application excel,

and want to save that .VBS again in script-able programming code.. irrespective of Syntax error anything..

Am I correct..!!!

 

[Prasoonj]

Hi Shrivallabha,

Code works !! .

Thanks a lot for your help. I'm happy to learn from you.

Hi Debraj,

Thanks for your reply . yes what you said is correct