Page 1 of 1
Excel 2010 vba Intellisense not working, want to make text b
Posted: 04 Jan 2012, 20:54
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
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 04 Jan 2012, 22:39
by Ron2K
Tannie Trouble can likely help you out on this one.
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 05 Jan 2012, 08:00
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)
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 08 Jan 2012, 09:23
by Mclaren
yes it was, thanks so much for the assistance.
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 08 Jan 2012, 09:24
by Mclaren
can the horizontal and vertical postions be located in relation to a specfic cell ?
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 08 Jan 2012, 09:31
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.
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 08 Jan 2012, 09:40
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.
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 09 Jan 2012, 21:26
by Mclaren
Thanks tribble
Re: Excel 2010 vba Intellisense not working, want to make te
Posted: 10 Jan 2012, 07:14
by Tribble
Pleasure - any time.