By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,246 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,246 IT Pros & Developers. It's quick & easy.

urgent: formatting XL cells

P: n/a
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"

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...b-net/200604/1
Apr 17 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

"mrid via DotNetMonster.com" <u16975@uwe> wrote in message
news:5ee7a69c9c6e9@uwe...
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"

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...b-net/200604/1

Apr 18 '06 #2

P: n/a
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!
+Vice wrote:
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.


--
it's all latin & geek to me! ;-)

Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...b-net/200604/1
Apr 18 '06 #3

P: n/a
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
mrid via DotNetMonster.com wrote:
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"

Apr 18 '06 #4

P: n/a
Give us some samples of the macros code you've created.

"latin & geek via DotNetMonster.com" <u16975@uwe> wrote in message
news:5ef2886ff9713@uwe...
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!
+Vice wrote:
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.


--
it's all latin & geek to me! ;-)

Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...b-net/200604/1

Apr 19 '06 #5

P: n/a
Vice: thank you for responding, problem resolved now.
Randy you were absolutely right! Thanks a million - that was a life saver! :)
R. MacDonald wrote:
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

[quoted text clipped - 51 lines]
.Cells(6, 2).Value = equip
.Cells(6, 1).Value = "Equip"


--
it's all latin & geek to me! ;-)

Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...b-net/200604/1
Apr 19 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.