urgent: formatting XL cells

  • Thread starter Thread starter mrid via DotNetMonster.com
  • Start date Start date
M

mrid via DotNetMonster.com

hi. im exporting data from a vb form to excel. i am able to create a new
excel file, save and edit it without any trouble, but the formatting is
giving me hell! i need to be able to show certain cells in bold, with
underlining etc. i dont understand how, but in the following code, the
instruction for bold gets picked up for all the cells (even when i specify
font.bold = false), and the underlining is haphazardly used. the font colour
formatting is also not getting picked up...
can someone please tell me how this thing works and what i should do?


With xlsheet
.Range("B1", "I50").RowHeight = 15
.Range("a1", "i50").ColumnWidth = 8.43

'title of sheet
.Cells(1, 1).Font.ColorIndex = 32
.Cells(1, 1).Style.font.size = 14
.Cells(1, 1).Font.Name = "Arial Black"
.Cells(1, 1).Value = "CONSUL"

'ISO form code
.Cells(2, 7).Font.ColorIndex = 1
.Cells(2, 7).Style.font.size = 10
MsgBox("ISO CODE")
.Cells(2, 7).Style.font.bold = True
.Cells(2, 7).style.font.underline = False
.Cells(2, 7).Font.Name = "Batang"
.Cells(2, 7).Value = "CRF - 02"

'branch
.Cells(2, 1).Style.font.name = "batang"
.Cells(2, 1).style.font.underline = False
MsgBox("branch underline")
.Cells(2, 1).style.font.bold = True
MsgBox("branch bold")
.Cells(2, 1).Style.font.size = 11
.Cells(2, 1).Value = ds.Tables("form1").Rows(ctr).Item("branch")

' form title
.Cells(3, 3).Style.font.underline = True
.Cells(3, 3).Style.font.size = 11
.Cells(3, 3).Value = "CONTRACT REVIEW CUM BDI FORM"

'se code
.Cells(6, 7).Style.font.size = 9
.Cells(6, 7).Style.font.bold = True
.Cells(6, 7).Value = "SE Code"
.Cells(6, 8).Style.font.size = 11
.Cells(6, 8).Value = ds.Tables("form1").Rows(ctr).Item("sec")

'equipment type
.Cells(6, 2).Style.font.size = 11
.Cells(6, 2).Font.ColorIndex = 15
.Cells(6, 2).Font.Name = "Arial black"
.Cells(6, 2).Value = equip
.Cells(6, 1).Value = "Equip"
 
If you have Excel and want to know how something is done, then Open Excel,
create a new worksheet, go Tools > Macro > Record new macro. Record it in
the workbook so you can just get rid of it when done testing. Now what ever
you want to know, just do that manually such as selecting a cell and setting
it's font to bold, etc. When done, stop the recording and open the Visual
Basic Editor under that same menu and open the procedure that was just
created by the macro recording and see how it's being done.
 
hi.

thanks for replying. i tried what you suggested, but am encountering a build
error when i try to use the macros code. eg. i get a
"system.data.range" is not accessible in this context because it is "private".


not very sure what that means! also, for some reason the border setting seems
to throw an error everytime! - even the macros code when pasted in the vb pgm
gets squiggly green lines.

could you please tell me if theres any other way to format those cells?
getting totally frustrated!
 
Hello, mrid,

I think that your problem is that you are changing the properties of the
"Style" that is applied to the cells. This will change the attribute
for all cells to which that style has been applied. (Unless you have
done something to change it outside of the code snippit shown, this is
probably the "Normal" style that you are changing, so it will affect all
cells.)

Just remove the ".Style" property everywhere in your code snippit, and I
think you will get what you want.

Cheers,
Randy
 
Vice: thank you for responding, problem resolved now.
Randy you were absolutely right! Thanks a million - that was a life saver! :)


R. MacDonald said:
Hello, mrid,

I think that your problem is that you are changing the properties of the
"Style" that is applied to the cells. This will change the attribute
for all cells to which that style has been applied. (Unless you have
done something to change it outside of the code snippit shown, this is
probably the "Normal" style that you are changing, so it will affect all
cells.)

Just remove the ".Style" property everywhere in your code snippit, and I
think you will get what you want.

Cheers,
Randy
hi. im exporting data from a vb form to excel. i am able to create a new
excel file, save and edit it without any trouble, but the formatting is
Click to expand...
[quoted text clipped - 51 lines]
.Cells(6, 2).Value = equip
.Cells(6, 1).Value = "Equip"
Click to expand...
Click to expand...
 
Back
Top