Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 19
Default Setting font properties using function

I want to be able to set the properties of a cell font using a function.
I have tried the below but it errors in the function with

Error Number: 91
Error Description: Object variable or With block variable not set

Anyone not on holiday out there who can help me please!

happy new year

Rod


------------------------
'Calling code

xlSht.Cells(1, 1).Font = NameFormatType1(255)


Private Function NameFormatType1(NmCol As Long) As Excel.Font

NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.Bold = True

End Function


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Setting font properties using function


Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
.Color = RGB(0, 0, NmCol)
.Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 19
Default Setting font properties using function

OK, that works fine. Thanks

Out of curiosity can my way be made to work.
It compiled OK so I was hopeful.



"joel" wrote in message
...

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
Color = RGB(0, 0, NmCol)
Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Setting font properties using function

'Calling code

xlSht.Cells(1, 1).Font = NameFormatType1(255)

Private Function NameFormatType1(NmCol As Long) As Excel.Font
NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.Bold = True
End Function


There are two problems with your originally posted code... one easily
solved, the second not. First, in your calling line of code, Font is an
object, so you must use the Set keyword to assign your function's returned
object to it. So that calling line should have looked like this...

Set xlSht.Cells(1, 1).Font = NameFormatType1(255)

Doing that, however, reveals the second problem... your function is declared
as an object, but it has no reference. That is, it's a font object, but a
font object to what? Obviously, you want it to reference A1 on xlSht, but
how is it to know that? Before you can change properties of NameFormatType1,
you first have to Set it to some object. In this case, you would need the
equivalent of this...

Set NameFormatType1 = xlSht.Cells(1, 1)

so it would know what object is having its properties modified. Of course,
having to do this would interfere with the approach you are trying to use.

--
Rick (MVP - Excel)


"Rod" wrote in message
...
OK, that works fine. Thanks

Out of curiosity can my way be made to work.
It compiled OK so I was hopeful.



"joel" wrote in message
...

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
Color = RGB(0, 0, NmCol)
Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help





  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 19
Default Setting font properties using function

Thanks
I understand the error message now.


"Rick Rothstein" wrote in message
...
'Calling code

xlSht.Cells(1, 1).Font = NameFormatType1(255)

Private Function NameFormatType1(NmCol As Long) As Excel.Font
NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.Bold = True
End Function


There are two problems with your originally posted code... one easily
solved, the second not. First, in your calling line of code, Font is an
object, so you must use the Set keyword to assign your function's returned
object to it. So that calling line should have looked like this...

Set xlSht.Cells(1, 1).Font = NameFormatType1(255)

Doing that, however, reveals the second problem... your function is
declared as an object, but it has no reference. That is, it's a font
object, but a font object to what? Obviously, you want it to reference A1
on xlSht, but how is it to know that? Before you can change properties of
NameFormatType1, you first have to Set it to some object. In this case,
you would need the equivalent of this...

Set NameFormatType1 = xlSht.Cells(1, 1)

so it would know what object is having its properties modified. Of course,
having to do this would interfere with the approach you are trying to use.

--
Rick (MVP - Excel)


"Rod" wrote in message
...
OK, that works fine. Thanks

Out of curiosity can my way be made to work.
It compiled OK so I was hopeful.



"joel" wrote in message
...

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
Color = RGB(0, 0, NmCol)
Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help










  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Setting font properties using function


Here is another method closer to what you did.


Type MyFont
Color As Long
Bold As Boolean
End Type
Sub test()
Dim Mycell As MyFont

Set xlSht = ActiveSheet
Mycell = NameFormatType1(255)
xlSht.Cells(1, 1).Font.Color = Mycell.Color
xlSht.Cells(1, 1).Font.bold = Mycell.bold

End Sub

Private Function NameFormatType1(NmCol As Long) As MyFont

NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.bold = True

End Function


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 19
Default Setting font properties using function

OK thanks, I see now.

"joel" wrote in message
...

Here is another method closer to what you did.


Type MyFont
Color As Long
Bold As Boolean
End Type
Sub test()
Dim Mycell As MyFont

Set xlSht = ActiveSheet
Mycell = NameFormatType1(255)
xlSht.Cells(1, 1).Font.Color = Mycell.Color
xlSht.Cells(1, 1).Font.bold = Mycell.bold

End Sub

Private Function NameFormatType1(NmCol As Long) As MyFont

NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.bold = True

End Function


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
setting label properties 81147leo Excel Programming 3 December 7th 07 09:57 AM
Setting worksheet properties ITQuestioner Excel Programming 3 August 24th 07 11:09 PM
Setting properties to combobox Stefan[_6_] Excel Programming 2 September 16th 04 12:21 PM
setting properties for shapes Hawk Excel Programming 3 May 21st 04 07:31 PM
Setting Font properties in Code clears Undo list Lorin Excel Programming 2 July 10th 03 09:49 PM


All times are GMT +1. The time now is 09:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"