PDA

View Full Version : Input box size



Gil
12-06-2011, 10:04 PM
Hello
I am trying to use an input box to insert several lines of data by pasting into the box. However I only get the first line. I also tried changing the range and active cell but only got the first line of data repeated.Obviously my efforts are not working. Do I need to add code or use another input method. Many thanks for any help on this matter.
Gil

Sub Macro1()

Range("P12").Select 'Select P12

MyData = InputBox("Enter job 1", "My Data", "Paste Here", 5000, 5000) ' Get user input

ActiveCell.Value = MyData ' place it in cell
End Sub

macropod
12-06-2011, 10:57 PM
Hi Gil,

Two issues:
• you can't paste Returns into an InputBox;
• InputBox strings are limited to 255 characters.

PS: your code would be more efficient as:
Sub Macro1()
Dim MyData As String
MyData = InputBox("Enter job 1", "My Data", "Paste Here", 5000, 5000) ' Get user input
Range("P12").Value = MyData ' place it in cell
End Sub

Gil
12-07-2011, 12:13 AM
Hello macropod
Thanks for the info, looks like its back to the drawing board for me. Hopefully someone will come up with a suggestion how I can enter several lines of data. I liked the input box because you could decide the cell it would be pasted in i.e if it was cell 'P12' and there were several lines it would populate 'P12, P13, P14, P15, P16, P17, P18.

Many thanks Gil

Gil
12-07-2011, 11:49 AM
Hello
Just got off my drawing board, I have now realised that maybe I was looking for something more complicated than necessary. What I have come up with is to use a command button with an assgned macro to paste anything on the clipboard to a designated range of cells, in this case IV1:IV40 (temporary storage area for some text). The code worked ok as long as I had something to paste but errored if there was nothing (I might forget to copy first before I use my command button). After a bit of reading I added 'On Error Resume Next' which goes past the error. It also said this was not good practice as it did not fix the problem. Can anyone guide me as to what code I should insert and maybe get a message box if there is nothing on the clipboard. A number of things have been tried but this is the best I have got so far.


Sub GetData()
Range (IV1:IV40).Select
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Cells(1, 1).Select

End Sub

mdmackillop
12-07-2011, 12:23 PM
You could insert Inputbox items with a separator, e.g. ";" and use the Split function to create an array which can be pasted into separate cells.

Gil
12-07-2011, 01:07 PM
Hello mdmackillop
Thank you for your reply but your sugestion is way above me. Could you get me started with a code example.
Gil

mdmackillop
12-07-2011, 01:23 PM
Sub Test()
Dim Data As String, d
Data = InputBox("Enter data separated by ';'", , "Test1;Test2;Test3;Test4")
d = Split(Data, ";")
Cells(1, 1).Resize(UBound(d) + 1) = Application.Transpose(d)
End Sub

Gil
12-07-2011, 02:02 PM
Hello mdmackillop
Thank you for the code. I have tried it and it works if I enter data manually but if I use the paste function only the first line is pasted. I have also tried seperating the data first with ; & ';' and the same result.
Gil

mdmackillop
12-07-2011, 02:24 PM
Why paste into an InputBox? Can you explain the basics of what you are trying to achieve?

Gil
12-07-2011, 03:47 PM
Hello mdmackillop
I want to be able to copy alpha numeric text from a non microsoft enviroment e.g a page from a book and use in an excel sheet. Once pasted into an excel sheet a macro will be run to pick out the words or numbers I require. As a page of data would look messy in view I want to paste it to a remote part of the sheet without going there. A macro would run to pick the bits I require and delete the remainder.
I tried the Input box first and found that it only allowed the first line to be pasted (my 1st post 7/12 5.04am) A reply from macropod told me no returns and up to 255 characters. I then looked at the task again and made my 3rd post 7/12 6.49pm.
As it looks like the input box will not quite serve my requirements it is my 3rd post I would like some help with.
I am now able to do almost all that I require but as I said I added 'On Error Resume Next' which goes past the error. It also said this was not good practice as it did not fix the problem, what code should I insert and maybe a message box if there is nothing on the clipboard.
I hope that makes sense
Gil

macropod
12-07-2011, 04:23 PM
Hi Gil,

Since the data are in the clipboard, you don't need an input box. You can simply parse the data directly. For example:
Dim MyData As DataObject, strClip As String, i As Long
Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
With ActiveSheet
MsgBox UBound(Split(strClip, vbCr))
For i = 0 To UBound(Split(strClip, vbCr))
.Range("P" & 12 + i).Value = Split(strClip, vbCr)(i)
Next
End With
The DataObject object is a part of the Forms library in VBA. To make this code work, you must either:
• Have at least one UserForm in your project, or
• In the VBA editor, go to Tools, References, and set a reference to the "Microsoft Forms 2.0 Object Library".

Gil
12-07-2011, 05:35 PM
Hello macropod
Thank you for the code supplied, using the 2nd option vba editor. This is what happens.
With data on the clipboard
1. MsgBox with number of rows of data being added i.e 23
2. Cell formats wrap text

No data on clipboard
1. MsgBox Run-timeerror '-2147221404(80040064)':
DataObject:GetTextInvalid FORMATETC structure

Many thanks Gil

macropod
12-07-2011, 05:49 PM
Hi Gil,

You can, of course, safely delete the Message Box.

The code isn't designed to prevent text wrapping - it's designed to parse text that includes its own line/paragraph breaks, which is how you described the issue in previous posts. If you want to prevent text wrapping whilst maintaining the existing column widths, that's going to take a lot more work.

I suspect your alleged run-time error for an empty clipboard is actually the result of the clipboard containing something that isn't text (eg a graphic). You can add error-checking if that's a risk. FWIW, the MsgBox returns '-1' if the clipboard really is empty and the code does not error-out.

Gil
12-07-2011, 06:44 PM
Hello macropod
Thank you for the reply, all of which I appreciate. The message box was a 'wish' if there was no data on the clipboard. The text wrapping is not really a problem but does not occur during normal pasting. I have no graphics that I am aware of and the -1 would not be ideal.
I refer back to my other post 07/12 06:49pm that is amost what I want and works, I know it's easy for me to say but doesn't it just need a little tidy up with a message box added if no data is on the clipboard. I have tinkered all night with that with no sucess. So here it is again
Many thanks
Gil


Sub GetData()
Range (IV1:IV40).Select
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues,Operation:=xlNone,SkipBlanks:=True,Transpose:=False,
Cells(1, 1).Select
End Sub

macropod
12-07-2011, 10:52 PM
Hello macropod
Thank you for the reply, all of which I appreciate. The message box was a 'wish' if there was no data on the clipboard. The text wrapping is not really a problem but does not occur during normal pasting. I have no graphics that I am aware of and the -1 would not be ideal.
I refer back to my other post 07/12 06:49pm that is amost what I want and works, I know it's easy for me to say but doesn't it just need a little tidy up with a message box added if no data is on the clipboard.
Many thanks
Gil
So what was all the stuff related to the use of an InputBox about? As for the message box, it plays no role in what gets pasted - if the clipboard is empty, nothing gets pasted. The code I posted should give you a few hints on how to test whether there's anything in the clipboard.

Gil
12-08-2011, 02:57 AM
Hello
Sorry, guilty as charged. The Input box was what I thought I would need to enter the data, it soon became clear from the replies that it was not going to handle multiple lines of data at once. If there was nothing to paste I thought a message saying nothing to paste would be nice.
Many thanks for all help and advice received.
Gil