| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset merged cell
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
offset merged cell
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
offset merged cell
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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
offset merged cell
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" wrote: 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" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merged cells .Offset(row,Col) inconsistancies | Excel Programming | |||
| cell.offset when starting cell is merged | Excel Programming | |||
| cell.offset when starting cell is merged | Excel Programming | |||
| Offset and Merged cells | Excel Programming | |||
| how to offset when it is a merged cell. | Excel Programming | |||
Hybrid Mode
