Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litXLS: using fonts and font attributes (Read 5461 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litXLS: using fonts and font attributes
03.11.05 at 10:54:34
 
Greg from the USA asked the following question about litXLS today:
 
Question:
When I set a cell to be bold all cells turn bold. How do I set an individual cell to bold?
 
Answer:
Please realise that there are only 4 fonts available. You can therefore make 4 versions of a font, e.g. one bold and one normal counts as 2 fonts.  
 
In the following example all two values will be turned bold. By default font 1 is attached to a cell so setting the font to be bold will turn all cells that have font 1 to bold:
Code:
  Dim xls As litXLS.clsXLS
  Dim wkb As litXLS.clsWorkbook
  Dim wks As litXLS.clsWorksheet

  'Instantiate litXLS
  Set xls = New litXLS.clsXLS
  Set wkb = xls.Workbooks.Add
  Set wks = wkb.Worksheets.Add

  wks.Cells(1,1).Value = "This is cell A1"
  wks.Cells(1,2).Value = "Hello World"

  wks.Cells(1,2).Font.Bold = True
 


 
If you want the first cell to be normal and the second to be bold you need to use a different font object for the cells:
Code:
  Dim xls As litXLS.clsXLS
  Dim wkb As litXLS.clsWorkbook
  Dim wks As litXLS.clsWorksheet
  
  'Instantiate litXLS
  Set xls = New litXLS.clsXLS
  Set wkb = xls.Workbooks.Add
  Set wks = wkb.Worksheets.Add
  
  wkb.Fonts(1).Bold = False
  wkb.Fonts(2).Bold = True

  wks.Cells(1,1).Value = "This is cell A1"
  wks.Cells(1,2).Value = "Hello World"

  Set Cells(1,1).Font = wkb.Fonts(1)
  Set Cells(1,2).Font = wkb.Fonts(2)
 


 
You can of course still use the syntax wks.Cells(1,1).Font.Bold = True but please realise that you will set the font to bold and all cells that have that font will turn bold.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
fpierz
YaBB Newbies
*




Posts: 2
Re: litXLS: using fonts and font attributes
Reply #1 - 26.01.06 at 18:10:47
 
I'm trying to use your bolding sample in the VB6 example that came with the dll, but on the statement Set Cells(1, 1).Font = wkb.Fonts(1) I get the error: sub or function not defined.  I'm not sure why. Any help would be greatly appreciated.  
 
Also, would I need to set all objects to Nothing before leaving the routine or just the objects that I have set with the New clause.
 
Thanks,
Frederick G. Pierz
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: litXLS: using fonts and font attributes
Reply #2 - 27.01.06 at 11:18:05
 
Dear Frederique,
 
Well spotted. I forgot to type the worksheet object prefix. I used Cells(1,1) but I should have used wks.Cells(1,1)
 
About setting the object variables back to Nothing is a good practise but it is not essential. I do suggest you set them to Nothing as you suggest. I have added this to my code too, thanks.
 
Code:
  Dim xls As litXLS.clsXLS
  Dim wkb As litXLS.clsWorkbook
  Dim wks As litXLS.clsWorksheet
  
  'Instantiate litXLS
  Set xls = New litXLS.clsXLS
  Set wkb = xls.Workbooks.Add
  Set wks = wkb.Worksheets.Add
  
  wkb.Fonts(1).Bold = False
  wkb.Fonts(2).Bold = True

  wks.Cells(1,1).Value = "This is cell A1"
  wks.Cells(1,2).Value = "Hello World"

  Set wks.Cells(1,1).Font = wkb.Fonts(1)
  Set wks.Cells(1,2).Font = wkb.Fonts(2)

  Set wks = Nothing
  Set wkb = Nothing
  Set xls = Nothing
 

Back to top
 
« Last Edit: 27.01.06 at 11:20:07 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1