Solved: InputBox Help needed

[jazzyt2u]

Hi,
If a cell length in column B is over 26 an input box shows them the current data and asks them to shorten the data.
Is there a way to either:
a) put the information that's in the cell into the inputbox so they only have to edit it instead of typing from scratch?
Or
b) is there some other source I can use aside from an inputbox that will capture what is in the cell and allow the user to edit and then replace the current information with the edited information?
Additionally can there be an input limit where it won't allow user to enter more than 26 characters?
What I have:

DescrpInput = InputBox("The current description:" & vbNewLine & vbNewLine & ActiveCell & vbNewLine & _
vbNewLine & "Is too long. Please shorten it to 26 characters including spaces.")
Do Until DescrpConfrm = 1
If Len(DescrpInput) > 26 Or DescrpInput = "" Then
Do Until Len(DescrpInput) < 27 And DescrpInput <> ""
If DescrpInput = "" Then
DescrpInput = InputBox("Please enter a replacement for:" & vbNewLine & vbNewLine & ActiveCell)
ElseIf Len(DescrpInput) > 26 Then
DescrpInput = InputBox("The description:" & vbNewLine & vbNewLine & DescrpInput & vbNewLine & _
vbNewLine & "Is still too long. Please shorten it by " & Len(DescrpInput) - 26 & " characters.")
End If
Loop
End If
DescrpConfrm = MsgBox("To confirm, the new description is:" & vbNewLine & vbNewLine & DescrpInput, vbOKCancel)
If DescrpConfrm = 2 Then
DescrpInput = InputBox("You canceled your entry. Please enter a replacement for:" & vbNewLine & vbNewLine & ActiveCell & vbNewLine & _
vbNewLine & "Or for the entered you just canceled:" & vbNewLine & vbNewLine & DescrpInput)
End If
Loop
rngT20.Select
ActiveCell = DescrpInput
Selection.AutoFill Destination:=rngTD20, Type:=xlFillCopy

[mdmackillop]

Can you use a UserForm for your data input? That could check/limit the length before entry.

[jazzyt2u]

Hi mdmackillop,
The data already exsists in the spreadsheet. They are cutting and pasting the data from QuatroPro. They are only editing the descriptions that are over 26 characters. They would prefer NOT to have to type everything over instead would like to have a popup with the current description in edit mode and delete words until it gets down to 26 characters. But if they did type it from scratch they would only be allowed to type 26 characters.
Can this happen with a userform and can the userform reside in the Macro? And be triggered to capture the data?
It would be awesome if I could at least paste information into an Inputbox and have the user delete words before they hit okay.

[mdmackillop]

Try this. Select a cell in column E

[mdmackillop]

A slightly different triggering code. The userform is opened if you paste long text into Column E

[jazzyt2u]

Yes!!! that is what I'm looking for. If I have the macro go row by row under Column E and if a cell's length is over 26 the box pops up rather than having the user click on a cell?

[jazzyt2u]

See the text is already in a spreadsheet and then the person has to shorten it before sending to another app... some cells will be within the 26 characters but others will be longer...

[mdmackillop]

Select the first long text in Col E. The code will go to the next long text after the shorter text is written to the sheet

[jazzyt2u]

Here is an example of the spreadsheet.
They the option to edit Description for each different Item. In this example on Item 1010000 and 103000 will need to be changed.

[jazzyt2u]

So it seems as though you either have to click on the cell to activate the userform or you have to paste into a column that has a userform behind it. Hummmm....
If those are the only ways I'll see if I can work with that... does the userform coding have to be in the same spreadsheet as the data it's manipulating?

[mdmackillop]

You can also use buttons, menu items, shortcut keys to trigger the userform. Based on your sample data I would approach things differently. I would call up long text to the userform and use Find/Replace to change all identical items. Is your sample typical of your real data?



The data already exsists in the spreadsheet. They are cutting and pasting the data from QuatroPro
See the text is already in a spreadsheet and then the person has to shorten it before sending to another app

These statements are confusing me as to the process involved.

[jazzyt2u]

Yes. It is. I appreciate your help so much!!!!!! Now I just have to learn userforms and I'll be on my way. I've only used forms in Access. But this has been very helpful.

[mdmackillop]

Try this

[jazzyt2u]

thanks works perfectly... you're awesome!!!!!