object required

[kyle]

i copied this from a book. i run the macro and it says object required.
what's up?

Private Sub cmdinsertheading_click()
Cells(1, "d").Value = txtheading.Text
Cells(1, "d").Select

With Selection
.Font.Bold = True
.Font.Name = "arial"
.Font.Size = 72
.Font.Color = RGB(0, 0, 255)
.Columns.AutoFit
.Interior.Color = RGB(0, 255, 255)
.Borders.Weight = xlThick
.Borders.Color = RGB(0, 0, 255)
End With

txtheading.Activate

End Sub

 

[JLGWhiz]

Cells(1, "d").Value = txtheading.Text

You are trying to put something in cell D1 that has not been defined in this
procedure.
txtheading appears to be a variable, but unless it is made a global variable
its value will not be picked up by a procedure that is private. The options
are to make it a global variable or to define it within this procedure.

 

[Jay]

The term “txtheading†is undefined; this produces the run-time error you
describe.

From the original code, I would guess that the text heading you are
manipulating in code is supposed to come from the contents of a cell
somewhere in the worksheet. Try this statement immediately after the
procedure’s opening statement:

Set txtheading = Range("M17") ‘substitute the cell address that contains
the heading text for “M17â€

 

[FSt1]

hi
what is txtheading.text?
sound like a textbox from a form.
also txtheading.activate......sounds like it's trying to activate the
form/textbox?
anyway txtheading is the object that vb needs/can't identfy.

regards
FSt1

 

[JLGWhiz]

Also, the use of the Cells property requires a qualified sheet reference if
the code is not in a sheet code module. It is a good practice to always
qualify cells property to be sure the range object on the correct sheet is
acted upon.


\

 

[kyle]

something still isn't right. i put this in the code

Set txtheading = Range("m17")

and what i type in m17 will appear in column d for a split second then
dissappear

 

[Jay]

Kyle -

After the procedure sets the value in D1, the combination of three things
scrolls the window and the entry seems to disappear (but it's still there).

1. ".Font.Size =72" makes the entry very large.
2. ".Columns.Autofit" widens the column to fit the large font.
3. "txtheading.Activate" forces cell D1 to scroll to the left (where you
can't see it).

So, rem out the statement txtheading.activate at the end and the procedure
should work, i.e., procede it with a single qoute ('txtheading.
Activate).