Solutions for Text Boxes in Excel

[garyj]

Hello...

I believe I was using an ActiveX textbox for the following. I would use a double click event on a particular column in a table to open a text box, link it to the target cell, and view the contents in a larger frame. The particular cell could contain a lot more text than I want the cell to show. It worked great in that I could edit the content in the larger textbox and then double click the text box an it would break the link to the cell and then close. I was also able to click on another cell in the next row for example, and the link would change to the new cell and the new text would show up leaving the changed text in the proper cell. Great stuff. Except that textbox was destroyed by a crash (I am thinking it was an ActiveX item, since it had a linking mechanism built in.)

Perhaps there are other ideas for similar functionality. I was looking for an alternate. One that I like, because it has the ability to show Bold, Italic, Underline, Font size, etc.. is the regular textbox in the Insert Menu of the Main Ribbon. I was able to handle it through this code..

'within the sheet DblClick event after testing for intersect
With sheets("Sheet1").textboxes("Textbox1")
            .Visible = True
             .Text = target.Value
             GblRemTarg = Target.address
            .Top = target.Top + target.Height + 1
            If GblBkgView = "HOS" Then .Left = 485 Else .Left = 230
            ActiveWindow.ScrollRow = ActiveCell.Row
            End With
... 

'However, this close event does not fire...

Private Sub TextBox1_DblClick(ByVal Cancel As ReturnBoolean)    'This trigger doesn't work. How can I trigger this? This is my main question.
    On Error Resume Next
    With TextBox1
        GblRemTarg.Value = .Text   'haven't tested this part yet, but will either need to make textbox modal or find a way to recall original target
        .Visible = False
        End With
End Sub

But it doesn't seem to respond to a double click (to close it). It doesn't link either, but for that there should be a workaround with code. Any ideas on how to trigger a double click event in the box?

Thanks
Gary

[garyj]

The single click event can be fired using "Assign Macro", and it works when it is first opened. I used a left click to get it into edit mode... but then the single click no longer worked to close it.. not even after exiting edit text mode. So this trigger event is clumsy to say the least.

[Aflatoon]

Non activex controls don't have events. You can only assign a macro to them. Also, although the textbox does support lots of font formatting, transferring it back and forth between the control and the cell requires reading each character one by one.

[garyj]

Thx, Alfatoon... good to know. There may be ways to solve this, but maybe I am trying too fancy.

The bare minimum is that the trip itinerary needs to be printed on the Trip Sheet, which is generated by dispatch and given to the driver. Everything else on the Trip Sheet comes from the record (bus no, driver, date, pick up location, destination, etc.) For some trips the client sends a much more detailed itinerary. So rather than type that into a cell, it is very possible that the necessary text portion be saved in a file such as filepath & tripno & ".rtf", or filepath & tripno & ".docx", or similar. It would be nice to use something other than notepad, for purposes of text formatting (bold, underscore).

Originally I wanted to draw a textbox on the spreadsheet at the size it will be on the Trip Sheet. User adds or edits text, and closes which saves it with Wordpad (smaller file). Then when it is time to print, the user selects the row, and the print button, which drops the data into the Trip Sheet, and then brings that rtf file and places it into a textbox, which I could also draw at that time, if necessary. Might even be better in case the user accidentally deletes it.

But if you were doing this kind of project, which method would you say is the simplest... and maybe I am staring at the trees for so long, I totally missed a path beside them?

(I am afraid to use ActiveX or OLE. Maybe my fear of OLE is misplaced. I read that ActiveX is Microsoft's idea for OLE, but really, I know so little.)

Gary

[Aflatoon]

From what you describe, I probably wouldn't be doing this in Excel. If you're going to be working with rtf/docx then Word would seem a logical choice. Even if you store the base data in Excel, it would probably make sense to use Word for the output.

[garyj]

Sorry this has taken a while to respond, Aflatoon, been busy, sick, busy.

Anyway, I wanted to say your idea was genius. Do the print from Word. So I set it up, most of the code already in place, which I would need anyway... to place the info from the selected row into the right named ranges in my excel "Trip Sheet". So kept that and made a Word file, kind of like a template. I copied the half of the excel "Trip Sheet" and special pasted as a link without format into the top half of the Word document. Then I made vba code to open a rich text file that stored the one long field [Trip Details], the itinerary really, and pasted that into the bottom half of the Word document. I was testing regular of course, fixing code, researching, took a day actually - partly because I was not well. And then something went awry, due to my sample RTF file being closed, but according to the Task Manager was still resident in the background... and my code suspended, crashed the system and lost my data. It's okay. The restored version came back without my extra work. Sigh. My VBA Word question has disappeared with the crash.

So I went back to what I had, because I had a previous solution. And since the purpose here is to provide learning for others as well, I figured I should come back and give my code that works for my original problem above. This is the bottom half of that code, and With Sheets("Trip Sheet") is in context.

It's a bit of a clumsy approach, but I found that if my textbox was selected when I did a Ctrl-V, the clipboard contents didn't go into the textbox, but into another one somewhere on the sheet. At first it was my nightmare. But then I got to thinking -- I can use that. So I tried it with the one picture on the sheet, a logo. It did the same. Perfect. So I got rid of my named textbox, chose to select that picture, pasted the clipboard (which kept the RTF format), and used 'Selection' to name and align that new textbox to the position where it would work.

I do have a side question, though and will send an upload so as not to confuse. // Not sure why it won't let me upload. I will post this and add the question below in another frame.

Thanks
Gary

        ...
        'Get Trip Details from Wordpad
        mystr = wsB.Cells(myrw, 1).Value   'Trip No for rtf file name
        DirFile = path2 & mystr & ".rtf"
        If Dir(DirFile) = "" Then
            myresp = MsgBox("There is no Wordpad file associated with the trip details on this booking. " & vbLf & _
                            "Do you wish to proceed with the print?", vbYesNo, "Note about selected trip")
            If myresp = 6 Then
                .Activate
                GoTo JmpWord
                Else: GoTo JmpOut
                End If
            End If
        With WordApp
            .Documents.Open Filename:=path2 & mystr & ".rtf"    'need code for no file exists
            .ActiveDocument.Select
            .Selection.Copy
            End With
        .Activate
        .Shapes("MyPicture").Select
        .Paste
        WordApp.Quit
        End With
    With Selection.ShapeRange
        .Name = "MyTextBox"
        .LockAspectRatio = msoFalse
        .Fill.Visible = msoFalse
        .Line.Visible = msoFalse
        .Left = 15
        .Top = 405
        .Width = 505
        .Height = 310
        End With
    Selection.Placement = xlFreeFloating
    Selection.PrintObject = True
JmpWord:
   wsT.Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
    Do  'stall code by looping until printing complete
        DoEvents
        Loop Until FindWindowEx(Application.Hwnd, 0, "FullpageUIHost", vbNullString) = 0
JmpOut:
    If shapeExists("MyTextBox", "Trip Sheet") Then wsT.Shapes("MyTextBox").Delete
    Sheets("Booking").Select
    Set WordApp = Nothing
    Set wsT = Nothing
    Set wsB = Nothing
End Sub

[garyj]

My other question...
Is there a reason for the delay in the opening of the RTF file? Is there something in the wrong order that would speed it up?
Since I cannot upload, here is the first part of the procedure, and a related one above it as I am on 365 with a 64bit system.
(The only procedure missing is a chunk in the middle that places data into the Trip Sheet. It is N/A to the issue.)

Option Explicit


#If VBA7 Then  'is this 64bit system
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
#Else
    'Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
#End If


Sub PrintTripsheet()
    Dim mydata As DataObject
    Dim wsB As Worksheet, wsT As Worksheet
    Set wsB = Sheets("Booking")
    Set wsT = Sheets("Trip Sheet")
    Dim WordApp As Object
    Set WordApp = CreateObject("Word.Application")
    Dim path2 As String, DirFile As String
    path2 = Sheets("Setup").Range("SetupTripDetsPath").Value
    If Intersect(ActiveCell, ActiveSheet.ListObjects("BkgTbl").DataBodyRange) Is Nothing Then Exit Sub
    Dim myrw As Integer, mycol As Integer, myresp As Integer
    Dim mystr As String
    myrw = ActiveCell.Row
    With wsT
     ...