offset merged cell

[Woodi2]

I have a youserform with 3 checkboxes. The checkbox data is entered onto the
sheet as follows
If CheckBox1.Value = True Then ActiveCell.Offset(0, 4) = "Mech"
If CheckBox1.Value = False Then ActiveCell.Offset(0, 4) = ""
If CheckBox2.Value = True Then ActiveCell.Offset(0, 4) = "Elect"
If CheckBox3.Value = True Then ActiveCell.Offset(0, 4) = "Inst/FET"

The ActiveCell is 3 cells that are merged. When i use the code above, it
will only write one of the 3 checkbox values into the offset cell even though
the offset cell is still 3 individual cells. How do I change this code so
that checkbox 1 enters data into the first cell, checkbox 2 enters data into
the cell below in the same column and checkbox 3 enters data into the 3rd
cell in the same row.
thanks

 

[FSt1]

hi
understand. with merged cells, the address of the merged cells takes the
address of the upper left cell. so... if your have merged cell of A1, A2 and
a3 then the address of the merged cells is always A1. so if A1 is the active
cell then offset(0,4) will always be E1.
so to accomplish what you want, you need to do something like this......
ActiveCell.Offset(0, 4).Value = "mech"
ActiveCell.Offset(1, 4).Value = ""
ActiveCell.Offset(2, 4).Value = "elect"
ActiveCell.Offset(3, 4).Value = "Inst/FET"

but since you have struck a nerve....
merged cell are about the dumbest thing that excel programers ever came up
with.
it may make the sheet look pretty.... but it screws everyting else
up....copy, paste, sort, ect. and you have now found yet another thing that
merged cells screws up.
my advice... get rid of the merged cells and your problems will mysteriously
go away and you wont be posting here looking for work arounds.

regards
FSt1

 

[Woodi2]

Thanks for the reply but unfortunately I need the merged cells otherwise the
sheet looks wrong.
I had already tied what you suggetsed but found this enters the result in
the rows below the merged cell, thus I still need an anwer.

 

[FSt1]

hi,
i did test before posting but after retest, it seems you are right. i just
found another way that merged cells can screw things up. grrrrrr. and your
statement confirms my opinion......

"but unfortunately I need the merged cells otherwise the
sheet looks wrong"

or as i stated....sort of......"don't look pretty"

try this.......
ActiveCell.Offset(0, 1).Offset(0, 4).Value = "mech"
ActiveCell.Offset(0, 1).Offset(1, 4).Value = ""
ActiveCell.Offset(0, 1).Offset(2, 4).Value = "elect"
ActiveCell.Offset(0, 1).Offset(3, 4).Value = "Inst/Fet"

maybe we can find some more ways that merged cells can screws things up.

regards
FSt1

 

[Woodi2]

Thanks gain but that did not work. I tried it and placed the mech in the
correct cell, but the other 2 were moved one row down and one column along.

 

[FSt1]

hi
i retested. works here. using xp/xl93. the other 2 should have moved down
one row because.....
ActiveCell.Offset(0, 1).Offset(1, 4).Value = ""
will prduces a blank cell between "Mech" and "Elect" but repeated tests does
not offset any columns.
sorry. is your activecell the merged cells?????

regards
FS1

 

[FSt1]

hi,
excuse.
using xp/xl03

typo.
(acursed fat fingers)
regards
FSt1

 

[Woodi2]

Yes, the activecell is the merged cell, the offset cells are individual
cells. If checkbox 1 is true then mech should be placed in offset 4, if
false the cell should be empty.
If checkbox 2 is true then mech should be placed in offset 4 and one row
down, if false the cell should be empty.
If checkbox 3 is true then mech should be placed in offset 4 and 2 rows
down, if false the cell should be empty.

There must be a way of offseting against a merged cell?

 

[FSt1]

hi
my bad. i forget to take into account the first offset.
try this.
ActiveCell.Offset(0, 1).Offset(0, 3).Value = "mech"
ActiveCell.Offset(0, 1).Offset(1, 3).Value = ""
ActiveCell.Offset(0, 1).Offset(2, 3).Value = "elect"
ActiveCell.Offset(0, 1).Offset(3, 3).Value = "Inst/Fet"

offset 1 + offset 3 = offset 4. so you were right. the columns were offset
one.

regards
FSt1

 

[Woodi2]

Thanks, it still did not quite work but lead me on the right track. With a
bit of searching and testing I have found that this works
If CheckBox1.Value = True Then ActiveCell.Offset(0, 4).MergeArea(1, 1).Value
= "mech"
If CheckBox2.Value = True Then ActiveCell.Offset(0, 4).MergeArea(2, 1).Value
= "elect"
If CheckBox3.Value = True Then ActiveCell.Offset(0, 4).MergeArea(3, 1).Value
= "Inst/Fet"

Thanks for your help.

 

[FSt1]

hi
good. nothing works better that sniffing out the right way on your own. you
learn more that way. glad i was able to point you in the right direction.
good luck.

Regards
FSt1