Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting label properties | Excel Programming | |||
Setting worksheet properties | Excel Programming | |||
Setting properties to combobox | Excel Programming | |||
setting properties for shapes | Excel Programming | |||
Setting Font properties in Code clears Undo list | Excel Programming |