Problem with a list box

RJC

Active Member
Joined
Jul 29, 2003
Messages
252
I have a List box embedded in my spreadsheet. When I execute a macro to move down to data which is defined below my main viewing area and separated by freezing the frames, and then use another macro to go back to the top, my list box contents including the scroll arrows, disappear. All I see are the columns underneath. The List box is linked to another
sheet in my workbook.

If I click the sheet tab for the other sheet, then the tab for my current sheet, the contents of the list box re-appear.

Can't I anchor the List Box, it's linking etc. so that it remains intact no matter how I move around on the rest of the sheet. I've tried just putting the reference to both sheets in my macro using [Sheets("Blah").Select] to switch sheets but this does not work. Only if I manually do it.

Does anyone know why this happens and is there a fix for it.

Cheers.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
This is why I don't care for ActiveX controls in general. I'll use them if I have to, but I'll get around using them if I can.

Have you considered using data validation instead? That way, your data is actually part of a cell, and you don't have to worry about linking it.
 
Upvote 0
RJC said:
Can't I anchor the List Box
You did not say if in fact this is an ActiveX ListBox or Forms ListBox. If the issue is how to anchor a ListBox or any control in a worksheet cell, so the control does not move around, then first, "snap" the control into an exact fit of the cell or range of cells by dragging it, or sizing it with the handles on its edges, while you have the Alt key pressed at the same time.

Next, right click on the ListBox (or if it is ActiveX, go into design mode then right click on it). From here, it doesn't matter if the ListBox is ActiveX or Forms: Select Format Control, and click onto the ListBox's Properties tab. Select the "Move and size with cells" option, click OK, then click onto any cell, exit design mode if it is ActiveX, and you are good to go.

If your question is something else altogether, sorry, please repost.
 
Upvote 0
Thanks Tom for the info.
I tried your suggestions but no luck. Also Tazguy37 gave me a clue but my data is on another sheet and I get a msg "You may not use references to other worksheets or workbooks for data validation criteria", so this is not a solution unfortunately.

Tom, I've already successfully linked my data using the forms ListBox in another sheet and it works perfectly. But on this sheet for some unknown reason, I can't get it to link properly. I can't see any difference at all to the settings between the sheets except for the linking cell. It's very frustrating.
:oops:
On my troublesome sheet, however, there are some clues to share.
1. If I right click within the sheet, the data is highlighted.
2. If I click the up down arrows, the outline (dark underline at bottom), disappears and the rest of the outline appears fainter. The shadow type underline reappears when I click anywhere else on the sheet.
3. If I move the cursor to the far right border, the up/down-right/left arrow icon appears.
None of this happens on my other working sheet.
It seems as if the ListBox is not set properly from these symptoms.

Tom, I'm still playing with the settings but if you have any other thoughts I would appreciate your assistance. Do you think changing the BoundColumn settings etc might be affecting it's behaviour.

Kind Regards,
RJC
 
Upvote 0
Hi Tom,
Sorry to bug you but I've got some more info on my problem.
I can anchor the List box and move up and down on my sheet whilst keeping everything intact except,
and this is where it fails.
I have my sheet set up with Freeze Panes and it is here where it fails. When I go down to my data area,
everthing works but when I go back to the top of my sheet, the area where my list box is, becomes
transparent and the linked data is not visible. If I simply move the horizontal bar to the right and then left,
the List box reappears. It must have something to do with the way the Panes are frozen. Even if I try
to refer to both sheets in my code, it won't work, only if I physically move the scroll bar.

Can you see anything I am doing wrong.

Cheers!
RJ


Here is my code:
To go down
Sub Entry()
ActiveWindow.FreezePanes = False
Range("B3").Select
ActiveWindow.LargeScroll Down:=1
ActiveWindow.SmallScroll Down:=-3
Range("C28").Select
Selection.End(xlDown).Select
Rows("29:29").Select
ActiveWindow.FreezePanes = True
Range("C28").Select
Selection.End(xlDown)(2, -1).Select
End Sub


To go back
Sub Up()
Application.ScreenUpdating = False
ActiveWindow.FreezePanes = False
Application.Goto Reference:="R1C2"
ActiveWindow.ScrollRow = 1
'Sheets("Blah1").Select Does not work
'Sheets("Blah2").Select
Range("B3").Select
Application.ScreenUpdating = True
ActiveWindow.LargeScroll ToRight:=0
End Sub




RJC said:
Thanks Tom for the info.
I tried your suggestions but no luck. Also Tazguy37 gave me a clue but my data is on another sheet and I get a msg "You may not use references to other worksheets or workbooks for data validation criteria", so this is not a solution unfortunately.

Tom, I've already successfully linked my data using the forms ListBox in another sheet and it works perfectly. But on this sheet for some unknown reason, I can't get it to link properly. I can't see any difference at all to the settings between the sheets except for the linking cell. It's very frustrating.
:oops:
On my troublesome sheet, however, there are some clues to share.
1. If I right click within the sheet, the data is highlighted.
2. If I click the up down arrows, the outline (dark underline at bottom), disappears and the rest of the outline appears fainter. The shadow type underline reappears when I click anywhere else on the sheet.
3. If I move the cursor to the far right border, the up/down-right/left arrow icon appears.
None of this happens on my other working sheet.
It seems as if the ListBox is not set properly from these symptoms.

Tom, I'm still playing with the settings but if you have any other thoughts I would appreciate your assistance. Do you think changing the BoundColumn settings etc might be affecting it's behaviour.

Kind Regards,
RJC
 
Upvote 0
…but my data is on another sheet and I get a msg "You may not use references to other worksheets or workbooks for data validation criteria", so this is not a solution unfortunately.
You can use a Validation drop down when the data is on another page providing the Validation source is a named range.

In the other page, name your range “myRange” (whatever).
Switch to your main page.
Insert a Validation drop down.
Allow: List.
Source: “=myRange” (no quotes).
OK.

Regards,

Mike
 
Upvote 0
Thanks for your help Mike,
Regards,
Rick :biggrin:


Ekim said:
…but my data is on another sheet and I get a msg "You may not use references to other worksheets or workbooks for data validation criteria", so this is not a solution unfortunately.
You can use a Validation drop down when the data is on another page providing the Validation source is a named range.

In the other page, name your range “myRange” (whatever).
Switch to your main page.
Insert a Validation drop down.
Allow: List.
Source: “=myRange” (no quotes).
OK.

Regards,

Mike
 
Upvote 0
Hello Mike,
I tried your suggestion which I'm sure works OK but I have a different circumstance to contend with. Perhaps you could assist again.

I'm loading data from the Net and using code which I don't fully understand. I just cobbled it together by trial and error basically, but it works, so I have no reason to change it. The code is below.............

The problem is that when my data loads into the spreadsheet, column A is hidden. Only B onwards is visible. I believe that I can only use one column as the anchor point in a data validation table, so does it have to be A and if so how do I set the references. Hope this is all clear to you.

Can you help?

Thanks Mike.

This is the part code that loads the web page. I just Dim the Entry_Web_Site as a string, then define the site then run this code.

With ActiveSheet.QueryTables.Add(Connection:="URL;" + Entry_Web_Site, Destination:=Range("A1"))
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With




Ekim said:
…but my data is on another sheet and I get a msg "You may not use references to other worksheets or workbooks for data validation criteria", so this is not a solution unfortunately.
You can use a Validation drop down when the data is on another page providing the Validation source is a named range.

In the other page, name your range “myRange” (whatever).
Switch to your main page.
Insert a Validation drop down.
Allow: List.
Source: “=myRange” (no quotes).
OK.

Regards,

Mike
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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