A Fast Way To Add Multiple Text boxes With Scrollbars and Add Original ID?

[Legacy 286866]

Hi All,

I have hit a bit of an issue when creating a system that keeps track of issues coming into an inbox (it’s kind of like a database of issues). Because the cells will contain so much data I have had to insert textboxes that have scrollbars to see all the data (they are linked to the cell which sits behind them on the spreadsheet. Is there any fast way to do the same thing on a column of 1000 records or will I have to go through manually and link the textbox to the specific cell? Is there a faster way?

Also If an issue comes in that is a reply to the original issue I need it to use the original ID (I have used auto IDS, which can be seen in the spreadsheet). Any recommendations?

Slowly I am getting better at excel but I need a hand sometimes ^_^

I have attached the spreadsheet which contains an example of 2 records I made. The final sheet will have 1000 records. (Please download the spreadsheet and open in excel)

https://drive.google.com/open?id=0B8OtUevU1V5sVkdndDd2UldxMHM

 

[AlphaFrog]

It may be easier to just have a UserForm with a textbox popup when you select a cell in column F

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_SelectionChange([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
    
        [COLOR=darkblue]If[/COLOR] Target.Value = "Send Email" [COLOR=darkblue]Then[/COLOR]
            r = Target.Row
            [COLOR=darkblue]With[/COLOR] CreateObject("Outlook.Application").CreateItem(0)
                .Subject = Cells(r, 5).Text
                .Body = "============" & vbNewLine & Cells(r, 7).Text & vbNewLine & "============" & vbNewLine & Cells(r, 6).Text
                .To = Cells(r, 4).Text
                .SentOnBehalfOfName = "7828691"
                .Display
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            
[B]        [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("F3:F1000"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]  [COLOR=green]'If selected cell within F3:F1000[/COLOR]
            [COLOR=darkblue]If[/COLOR] Len(Target.Value) > 0 [COLOR=darkblue]Then[/COLOR] UserForm1.Show
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/B]
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

The userform auto-populates its textbox from the active cell in column F

UserForm code module:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    Me.TextBox1.Text = ActiveCell.Value
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    Unload Me
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I don't know about your ID question.

 

[Legacy 286866]

Hi Alpha Frog,

Thanks for the reply. I have been playing around with this a bit and got it to work really well. Sorry for the late reply and as always you rock mate.

Looking into the ID question what I think maybe best is if a number exists in the subject line it puts nothing in the ID column (so no number). My issue is if there are 2 blank sections in ID due to it finding an ID number in the Title area how will it know to follow with the correct logical number for a new case?

Attached an image as an example (I would have attached this as an image but for some reason you cant post from Google images using the image selector above).

https://goo.gl/photos/H19vnbJGmYtAyuQVA