Combo Box Mysteriously moves with Grouped Cells

J

jefferyshall

New Member
Joined
Dec 16, 2022
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I have a long list grouped by State. I have an empty "header" row a top each State group so no actual data rows show when collapsed. I added a ComboBox in one of the cells of each row 'except' for the empty header rows. When I collapse the Groups for some reason the last cell from each group moves down into the empty header row. I have stopped this from showing by adding a completely empty row at the bottom of each States group, but wondering why the ComboBox moves down one spot in the first place? And obviously if there is a way to stop it from doing so.

I have tried making that last row with a ComboBox on it a bit taller thinking maybe it's because the ComboBox seems to over hang the bottom of the cell a little bit, but that didn't do anything.

MiniSheet below
All VETS RG Delivery List.xlsx
ABCDEF
1Full NameJob TitleDepartmentWork CityStateDeliver to
2Arkansas
3Little RockAR
4Arizona
5MiamiAZ
6Oro ValleyAZ
7PhoenixAZ
8PhoenixAZ
9PhoenixAZ
10PhoenixAZ
11PhoenixAZ
12PhoenixAZ
13PhoenixAZ
14PhoenixAZ
15PhoenixAZ
16PhoenixAZ
17PhoenixAZ
18PhoenixAZ
19PhoenixAZ
20PhoenixAZ
21PhoenixAZ
22PhoenixAZ
23PhoenixAZ
24PhoenixAZ
25PhoenixAZ
26PhoenixAZ
27PhoenixAZ
28PhoenixAZ
29TempeAZ
30TempeAZ
31TempeAZ
32TempeAZ
33TempeAZ
34TempeAZ
35TempeAZ
36TempeAZ
37TempeAZ
38TempeAZ
39TempeAZ
40TempeAZ
41TempeAZ
42TempeAZ
43TempeAZ
44TempeAZ
45TempeAZ
46TempeAZ
47TempeAZ
48TempeAZ
49TempeAZ
50TempeAZ
51TempeAZ
52TempeAZ
53TempeAZ
54TempeAZ
55TempeAZ
56TempeAZ
57TempeAZ
58TempeAZ
59TempeAZ
60TempeAZ
61TempeAZ
62TempeAZ
63TempeAZ
64TempeAZ
65TempeAZ
66TempeAZ
67TempeAZ
68TempeAZ
69TempeAZ
70TempeAZ
71TempeAZ
72TempeAZ
73TempeAZ
74TucsonAZ
75TucsonAZ
76TucsonAZ
77California
78Beverly HillsCA
79CapitolaCA
80CarmichaelCA
81Castro ValleyCA
82Citrus HeightsCA
83City of IndustryCA
84City of IndustryCA
85EmeryvilleCA
86FresnoCA
87FresnoCA
88HealdsburgCA
89LafayetteCA
90LockefordCA
91Los AngelesCA
92Los AngelesCA
93Los AngelesCA
94Los AngelesCA
95Los AngelesCA
96Mission ViejoCA
97NewmanCA
98Newport BeachCA
99Newport BeachCA
100Newport BeachCA
101Newport BeachCA
102Newport BeachCA
103OaklandCA
104OxnardCA
105RosevilleCA
106SacramentoCA
107SacramentoCA
108SacramentoCA
109SacramentoCA
110SacramentoCA
111SacramentoCA
112SacramentoCA
113SacramentoCA
114SacramentoCA
115SacramentoCA
116SacramentoCA
117SacramentoCA
118SacramentoCA
119SacramentoCA
120SacramentoCA
121SacramentoCA
122SacramentoCA
123SacramentoCA
124SacramentoCA
125SacramentoCA
126SacramentoCA
127SacramentoCA
128SacramentoCA
129SacramentoCA
130SacramentoCA
131SacramentoCA
132SacramentoCA
133SacramentoCA
134SacramentoCA
135SacramentoCA
136SacramentoCA
137SacramentoCA
138SacramentoCA
139SacramentoCA
140SacramentoCA
141SacramentoCA
142SacramentoCA
143SacramentoCA
144SacramentoCA
145SacramentoCA
146SacramentoCA
147SacramentoCA
148SacramentoCA
149SacramentoCA
150SacramentoCA
151SacramentoCA
152SacramentoCA
153SacramentoCA
154SacramentoCA
155SacramentoCA
156SacramentoCA
157SacramentoCA
158SacramentoCA
159SacramentoCA
160SacramentoCA
161SacramentoCA
162SacramentoCA
163SacramentoCA
164SacramentoCA
165SacramentoCA
166SacramentoCA
167SacramentoCA
168SacramentoCA
169SacramentoCA
170SacramentoCA
171SacramentoCA
172SacramentoCA
173SacramentoCA
174SacramentoCA
175SacramentoCA
176SacramentoCA
177SacramentoCA
178SacramentoCA
179SacramentoCA
180SacramentoCA
181SacramentoCA
182SacramentoCA
183SacramentoCA
184SacramentoCA
185SacramentoCA
186SacramentoCA
187SacramentoCA
188SacramentoCA
189SacramentoCA
190SacramentoCA
191SacramentoCA
192SacramentoCA
193SacramentoCA
194SacramentoCA
195SacramentoCA
196SacramentoCA
197SacramentoCA
198San FranciscoCA
199San FranciscoCA
200San FranciscoCA
201San FranciscoCA
202San FranciscoCA
203San FranciscoCA
204San FranciscoCA
205San FranciscoCA
206San FranciscoCA
207San FranciscoCA
208San FranciscoCA
209San FranciscoCA
210San FranciscoCA
211San FranciscoCA
212San FranciscoCA
213San FranciscoCA
214San FranciscoCA
215San JoseCA
216San JoseCA
217San JoseCA
218San RamonCA
219San RamonCA
220San RamonCA
221San RamonCA
222San RamonCA
223San RamonCA
224San RamonCA
225San RamonCA
226San RamonCA
227San RamonCA
228San RamonCA
229San RamonCA
230San RamonCA
231San RamonCA
232San RamonCA
233San RamonCA
234San RamonCA
235San RamonCA
236San RamonCA
237San RamonCA
238San RamonCA
239San RamonCA
240San RamonCA
241San RamonCA
242San RamonCA
243San RamonCA
244San RamonCA
245San RamonCA
246San RamonCA
247San RamonCA
248San RamonCA
249San RamonCA
250San RamonCA
251San RamonCA
252San RamonCA
253San RamonCA
254San RamonCA
255San RamonCA
256San RamonCA
257San RamonCA
258San RamonCA
259San RamonCA
260San RamonCA
261San RamonCA
262San RamonCA
263San RamonCA
264San RamonCA
265San RamonCA
266San RamonCA
267San RamonCA
268San RamonCA
269San RamonCA
270San RamonCA
271San RamonCA
272San RamonCA
273San RamonCA
274San RamonCA
275San RamonCA
276San RamonCA
277San RamonCA
278San RamonCA
279San RamonCA
280San RamonCA
281San RamonCA
282San RamonCA
283San RamonCA
284San RamonCA
285San RamonCA
286San RamonCA
287San RamonCA
288SaratogaCA
289SebastopolCA
290Sherman OaksCA
291Sierra MadreCA
292StocktonCA
293TorranceCA
294VacavilleCA
295VacavilleCA
296Colorada
297AuroraCO
298BoulderCO
299BrightonCO
300BroomfieldCO
301DenverCO
302DenverCO
All RG Members
 

Attachments

  • ComboBoxes - Groups not collapsed.png
    ComboBoxes - Groups not collapsed.png
    71.4 KB · Views: 8
  • ComboBoxes - Groups are collapsed.png
    ComboBoxes - Groups are collapsed.png
    21.8 KB · Views: 9

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
The combo box needs to move and size with cells.

RIght click on it,
Format Control
Properties

1671252629657.png


To do all of them at once>
F5
Special
Objects
Then the above
 
Upvote 0
OK, so I thought I had stopped this from happening by adding one completely empty line at the end of each group, but it didn't fix it. Even with the extra blank row at the end of every group when groups are collapsed every State group header row still has the last ComboBox of the previous group moved onto it.

First image below shows everything collapsed and all ComboBoxes were empty, then while collapsed I picked values for the last six State headers (where no ComboBoxes should be).
Each image after that shows where I start to expand each group from the bottom you can see it's definitely the last ComboBox from the previous group, even with the two rows between.
 

Attachments

  • ComboBoxes - Last groups open with items picked.png
    ComboBoxes - Last groups open with items picked.png
    52 KB · Views: 6
Upvote 0
gelu said:
The combo box needs to move and size with cells.

RIght click on it,
Format Control
Properties

View attachment 81103

To do all of them at once>
F5
Special
Objects
Then the above
Click to expand...
That option was grayed out when created them in the first place (and still is), so I picked "Move , but don't resize with cells". I figured that should still work because I am not resizing sizing the cells, just moving them when the lists collapse. Which seems to kind of work because they are not staying in place while the row beneath are collapsing, they are collapsing with it except for the last cell.

I have also experimented with having data in the cell to see if it is only overwriting because its empty, but it still overwrites with text in the cell. The only thing that makes it stop is if I have the a ComboBox in the cell, then it won't overwrite it and everything collapses properly except I am left with all those blank rows having the ComboBox.
 
Upvote 0
jefferyshall said:
That option was grayed out when created them in the first place (and still is), so I picked "Move , but don't resize with cells". I figured that should still work because I am not resizing sizing the cells, just moving them when the lists collapse. Which seems to kind of work because they are not staying in place while the row beneath are collapsing, they are collapsing with it except for the last cell.

I have also experimented with having data in the cell to see if it is only overwriting because its empty, but it still overwrites with text in the cell. The only thing that makes it stop is if I have the a ComboBox in the cell, then it won't overwrite it and everything collapses properly except I am left with all those blank rows having the ComboBox.
Click to expand...
So I figured out how to get the Option to set it to "Move and Resize with cells". It is still grayed out when you right click and go to the Properties that way, but you can do it by right clicking on it and while right clicked the "FORMAT" tab appears in the ribbon above, go up and click the Format tab, then in the "Size" group click the small arrow in the lower right corner, this opens the Properties panel to the right, go to Format & Shape (last icon at the top), go to Properties section and NOW you can pick "Move and size with cells". *No idea why, it is definitely the same set of Properties, because when I go and just right click them I can now see Move and Resize is picked (still grayed out though!) and if I change it in the right click properties to one of the other choices the change is represented in the still open Format Properties panel too, so why it is allowed to be changed in one properties panel and not the other...?? Definitely some weird bug.

Anyways - I changed them all and it still does the same thing. I kind of figured it would since in this situation I am not resizing, so the first two choices (at least to me) should work the same.

Hmmm... although, I do notice now that the first three rows aren't over lapping now, but the rest still are. I double checked and they are all set to Move and Resize with cells. Weird!
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Move Row to Another Sheet Based on Multiple Cell Values
Replies
9
Views
446
jolivanes
jolivanes
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
298
kvsrinivasamurthy
K
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
202
igold
igold
I
  • Solved
Move selected row in table to row 3
Replies
2
Views
230
ipbr21054
I
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
327
RoryA
RoryA

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top