locked
Textbox RRS feed

  • Question

  • Hi,

    VB.NET - Is it possible to add vertical scrollbar to Excel textbox(Shape) which is not a form control?

    Thanks,




    • Edited by wes.r Thursday, March 27, 2014 7:24 PM
    Thursday, March 27, 2014 12:38 PM

Answers

  • Hi,

    If you want to show the vertical scrollbar to Excel when you open Excel, you can refer to code below:

    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class InsertMutiLineTextbox
        Public Sub Main()
            Dim excelAPP As New Excel.Application
            Dim aWorkbook As Excel.Workbook
            Dim aWorksheet As Excel.Worksheet
            Dim targetRagne As Excel.Range
            Dim myTextbox As Excel.Shape
    
            excelAPP.Visible = True
            aWorkbook = excelAPP.Workbooks.Open("C:\Users\UserName\Desktop\test.xlsx")
            aWorksheet = aWorkbook.Worksheets(1)
    
            targetRagne = aWorksheet.Range("A1")
            myTextbox = aWorksheet.Shapes.AddOLEObject(ClassType:="Forms.TextBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=targetRagne, Top:=targetRagne.Top, Width:=targetRagne.Width, Height:=targetRagne.Height * 2)
    
            myTextbox.Name = "test"
    
            aWorksheet.OLEObjects("test").Object.MultiLine = True
            aWorksheet.OLEObjects("test").Object.Value = "a" & vbCr & vbLf & "b" & vbCr & vbLf & "c"
            aWorksheet.OLEObjects("test").Object.ScrollBars = 3
            aWorksheet.OLEObjects("test").Object.ScrollBars = 2
    
            aWorksheet.OLEObjects("test").Verb(1)
            aWorksheet.OLEObjects("test").Select()
    
            'aWorkbook.Save()
            'excelAPP.Quit()
        End Sub
    
    
    End Class
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by wes.r Thursday, April 10, 2014 12:16 PM
    Wednesday, April 9, 2014 12:37 PM
  • Hi Wesley,

    Did you mean you want to format the text in the textbox control? If I understand correctly, we can't achieve the goal. The textbox control only can display the plain text.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by wes.r Thursday, April 10, 2014 12:16 PM
    Thursday, April 10, 2014 9:20 AM

All replies

  • Hi,
    Does the textbox mean that you inserted like figure below:

      


    If I understood correctly, it is a shape like you pointed. We can’t add the vertical scrollbar for it. As a workaround we can add a textbox control (Windows Forms Control) like code below:

    private void button1_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Worksheet workSheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet; Microsoft.Office.Tools.Excel.Worksheet workSheet1; workSheet1 =Globals.Factory.GetVstoObject(workSheet); TextBox textbox1=new TextBox(); textbox1.Name="textbox1"; textbox1.Text = "A\r\nB\r\nC\r\n"; textbox1.Multiline=true; textbox1.ScrollBars=ScrollBars.Vertical; workSheet1.Controls.AddControl(textbox1, workSheet.Range["A1"], textbox1.Name); }

    Screen Shot:


    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 28, 2014 3:33 AM
  • Hi Fei,

    Thank you for your replay. I'm having a hard time implementing your code in vb.net. See errors below

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
            Dim workSheet1 As Excel.Worksheet
            workSheet1 = Globals.Factory.GetVstoObject(workSheet)
            Dim textbox1 As New TextBox()
            textbox1.Name = "textbox1"
            textbox1.Text = "A" & vbCr & vbLf & "B" & vbCr & vbLf & "C" & vbCr & vbLf
            textbox1.Multiline = True
            textbox1.ScrollBars = ScrollBars.Vertical
            workSheet1.Controls.AddControl(textbox1, workSheet.Range("A1"), textbox1.Name)
        End Sub

    Error 2 'ThisAddIn' is not a member of 'Microsoft.VisualBasic.Globals'. 

    Error 3 'Factory' is not a member of 'Microsoft.VisualBasic.Globals'. 

    Error 4 Option Strict On disallows late binding. 

    Thanks,


    wesley

    Friday, March 28, 2014 2:02 PM
  • Hi wesley,

    To use this workaround, we need to create a Excel add-in. We can create the project like figure below:

    Then we can add a user control to the project and add a button on the userform.

    And put code below to insert the textbox in the click event of the button:

      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim workSheet1 As Microsoft.Office.Tools.Excel.Worksheet
    
            workSheet = (Globals.ThisAddIn.Application.ActiveSheet)
            workSheet1 = Globals.Factory.GetVstoObject(workSheet)
            Dim textbox1 As New TextBox
            textbox1.Name = "textbox1"
            textbox1.Text = "A" & vbCr & vbLf & "B" & vbCr & vbLf & "C" & vbCr & vbLf
            textbox1.Multiline = True
            textbox1.ScrollBars = ScrollBars.Vertical
            workSheet1.Controls.AddControl(textbox1, workSheet.Range("A1"), textbox1.Name)
    
        End Sub

    At last add code below in the ThisAddIn_StartUp sub to show the user control:

      Private Sub ThisAddIn_Startup() Handles Me.Startup
            Dim ctp As CustomTaskPane
            ctp = Me.CustomTaskPanes.Add(New UserControl1(), "TaskPane")
            ctp.Visible = True
        End Sub

    In addition, link below is also helpful:

    Office Solutions Development Overview

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 1, 2014 8:42 AM
  • HI Fei,

    How would I implement this addin within the project without using the button as shown in your example?

    Thanks, 


    wesley

    Monday, April 7, 2014 6:57 PM
  • Hi Wesley,

    What do you mean without using the button? If there is no button, how do you want to execute the code?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 8, 2014 6:48 AM
  • Hi Fei,

    I have  a function that is responsible for adding an image and related image info to sheet. Below code snippet extracts image tags and returns them into sCaptionResult string which is used to  populate the text box.

    Within below function, I want to be able to replace txtBox object with add-in control.

    Dim pictureShape As Excel.Shape

    With oXL.Sheets("Images") .Shapes.AddPicture(filePath & "\" & range2.Value, _ MsoTriState.msoFalse, _ MsoTriState.msoTrue, cellRange.Left, cellRange.Top, 500, 300) End With pictureShape = oXL.Sheets("Images").Shapes(oXL.Sheets("Images").Shapes.Count) If cellRange.RowHeight < pictureShape.Height Then cellRange.RowHeight = pictureShape.Height + 26 End If Dim sCaptionResult As String = GetFreeImageTag("Caption") If sCaptionResult.Length > 0 Then If cellRange.ColumnWidth < pictureShape.Width Then Dim col As String = Split(cellRange.Address, "$")(1) oXL.Sheets("Images").Columns(col & ":" & col).ColumnWidth = (200 + pictureShape.Width) / 5.25 + 4 cellRange.Value = range2.Value End If Dim captionTiltle As String = Split(sCaptionResult, vbLf)(0) Dim txtBox As Object = oXL.Sheets("Images").Shapes.Addtextbox(MsoTextOrientation.msoTextOrientationHorizontal, _ pictureShape.Left, _ pictureShape.Top, _ 200, _ pictureShape.Height) txtBox.Textframe.Characters.Text = sCaptionResult With txtBox.Textframe.Characters(Start:=1, Length:=captionTiltle.Length).Font .ColorIndex = 5 .Bold = True End With pictureShape.IncrementLeft(200) Dim grpObj As Object() = New Object() {txtBox.Name, pictureShape.Name} oXL.Sheets("Images").Shapes.Range(grpObj).Group() End If




    • Edited by wes.r Tuesday, April 8, 2014 1:04 PM
    Tuesday, April 8, 2014 12:55 PM
  • Hi,

    If you want to show the vertical scrollbar to Excel when you open Excel, you can refer to code below:

    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class InsertMutiLineTextbox
        Public Sub Main()
            Dim excelAPP As New Excel.Application
            Dim aWorkbook As Excel.Workbook
            Dim aWorksheet As Excel.Worksheet
            Dim targetRagne As Excel.Range
            Dim myTextbox As Excel.Shape
    
            excelAPP.Visible = True
            aWorkbook = excelAPP.Workbooks.Open("C:\Users\UserName\Desktop\test.xlsx")
            aWorksheet = aWorkbook.Worksheets(1)
    
            targetRagne = aWorksheet.Range("A1")
            myTextbox = aWorksheet.Shapes.AddOLEObject(ClassType:="Forms.TextBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=targetRagne, Top:=targetRagne.Top, Width:=targetRagne.Width, Height:=targetRagne.Height * 2)
    
            myTextbox.Name = "test"
    
            aWorksheet.OLEObjects("test").Object.MultiLine = True
            aWorksheet.OLEObjects("test").Object.Value = "a" & vbCr & vbLf & "b" & vbCr & vbLf & "c"
            aWorksheet.OLEObjects("test").Object.ScrollBars = 3
            aWorksheet.OLEObjects("test").Object.ScrollBars = 2
    
            aWorksheet.OLEObjects("test").Verb(1)
            aWorksheet.OLEObjects("test").Select()
    
            'aWorkbook.Save()
            'excelAPP.Quit()
        End Sub
    
    
    End Class
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by wes.r Thursday, April 10, 2014 12:16 PM
    Wednesday, April 9, 2014 12:37 PM
  • Hi Fei,

    I've been able to implement the vertical bars using your example.

    One other question, I would like to make the title using bold font, change ColorIndex and change BorderStyle to FixedSingle . Is there something that is equivalent of the below below example.

    With txtBox.Textframe.Characters(Start:=1,  Length:=captionTiltle.Length).Font
        .ColorIndex = 5
        .Bold = True
    End With
    Thank you very much,


    wesley


    • Edited by wes.r Wednesday, April 9, 2014 2:06 PM
    • Marked as answer by wes.r Thursday, April 10, 2014 12:16 PM
    • Unmarked as answer by wes.r Thursday, April 10, 2014 12:16 PM
    Wednesday, April 9, 2014 1:53 PM
  • Hi Wesley,

    Did you mean you want to format the text in the textbox control? If I understand correctly, we can't achieve the goal. The textbox control only can display the plain text.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by wes.r Thursday, April 10, 2014 12:16 PM
    Thursday, April 10, 2014 9:20 AM