Excel 2010 vba Intellisense not working, want to make text b

Get help on programming - C++, Java, Delphi, etc.
Post Reply
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Excel 2010 vba Intellisense not working, want to make text b

Post by Mclaren »

So as my title says, Excel 2010 vba Intellisense is not working, and i want to insert a text box using code.

My problem is, i have no idea how to do this.

I have successfully recorded a macro, but the code means nothing to me.

In Access my intellisense works, but bot in excel. with intellisense one can use it as a tool to understand the functions.

If anyone can help me understand the code below, i would appreciate it.

Code: Select all

Sub InsertTextBox()
'
' InsertTextBox Macro
' Insert text box
'
' Keyboard Shortcut: Ctrl+t
'
    ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 60, 213, 72, 72). _
        Select
    Selection.ShapeRange.ScaleWidth 10, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Hospital Signatures:" & Chr(13) & "" & Chr(13) & "Stock Controller (or equivelant):___________________" & Chr(13) & "" & Chr(13) & "Pharmacy Manager:_________________Date:___________"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 21).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 21).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        
    End With
    
    

End Sub
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Ron2K »

Tannie Trouble can likely help you out on this one. ;)
Kia kaha, Kia māia, Kia manawanui.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Tribble »

Well she can try.

Macros are useful for getting the general gist of what is needed but they do more than you would actually do in code. But it is a good start.

Here is something a bit more helpful

Code: Select all

Sub AddTextBox()
    ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 2.5, 1.5, _
        116, 145).TextFrame.Characters.text = "This is a test of inserting a text box to a sheet and adding some text."
    With Selection.Characters(Start:=1, Length:=216).font
        .name = "Arial"
        .FontStyle = "Regular"
        .size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    range("I15").Select
End Sub
I am lazy and do not add all the text qualities but to be safe - you should. Especially if someone else will be using your sheets.

The first figure determines horizontal position. The second is for vertical.
The 116 is the width and the 145 is the height
ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 2.5, 1.5, _
116, 145).TextFrame.Characters.text = "This is a test of inserting a text box to a sheet and adding some text."
This tells the system what you want - how big it must be and what text you want in it. Play with the figures until you get the size you want.

The next bit I am sure you understand tells the program what you want the text in the box to look like.


Was that helpful? (I sound like those darn Windows help screens)
Image
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Mclaren »

yes it was, thanks so much for the assistance.
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Mclaren »

can the horizontal and vertical postions be located in relation to a specfic cell ?
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Tribble »

Let me look into that. I think you would have to declare it or use some code to locate the active cell. Was wondering this myself.
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Tribble »

Oooh I haven't tested this yet but this is what Google showed me

Code: Select all

With ActiveSheet
  .Shapes.AddTextbox msoTextOrientationHorizontal, .Cells(3,2).Left, .Cells(3,2).Top, .Cells(3,2).Width, .Cells(3,2).Height
End With
This will put it over cell B3 and make it the same size as B3. Remember the row comes first and then the column. This way you can work out where to put it.
Image
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Mclaren »

Thanks tribble
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel 2010 vba Intellisense not working, want to make te

Post by Tribble »

Pleasure - any time.
Image
Post Reply