• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Wonky dropdown lists...but only on some sheets in the workbook

P

Peacedout

Member
Some time ago, I used code I found at another website to make dropdown lists with a larger font and many items to choose from other than those that are allowed in a basic Excel 2007 dropdown list.

I copied the code to each sheet in the workbook. The code WORKS, but on some of the sheets, the dropdown lists don't appear at the cell: they are on the far left (or middle left) of the page.

The code on each sheet appears to be the same (except for the combo box name). Why is this happening on some of the sheets but not others?
 
NARAYANK991 said:
Hi ,

Can you upload your workbook with at least one instance where it works and another where it does not ?

Narayan
Click to expand...

Although I've deleted a bunch of stuff from the workbook and it appears to be only 3400 kb, I keep getting an error that the file is too large for the server to process.
 
As I was deleting sheets to reduce the workbook size, I started to mess around with freeze/unfreeze panes and this seems to affect the location of the dropdown lists. e.g. I have freeze panes engaged on a sheet. Dropdown list appears on far left of screen instead of under cell. Unfreeze panes and dropdown list appears where it should. Re-freeze panes and dropdown list remains where it should. I THINK I'M ON TO SOMETHING, HERE.... :)
 
Your code needs to set the Sheet to Unprotect or Protect like this to allow code to make changes. Obviously, even then if you lock cells, then the user can not interact with them.
Code: 
Sheet1.Protect "YourPasswordHereOrBlankQuotes", UserInterfaceOnly:=True
 
Peacedout said:
Although I've deleted a bunch of stuff from the workbook and it appears to be only 3400 kb, I keep getting an error that the file is too large for the server to process.
Click to expand...
Hi ,

The limit in this forum for file sizes is 1 MB. Clearly , you cannot upload your workbook here.

What you can do is use any public file sharing site such as DropBox , upload your file there , create a share link which gives others permission to access , download and open your file , and then post that link in this thread.

Narayan
 
Kenneth Hobson said:
Your code needs to set the Sheet to Unprotect or Protect like this to allow code to make changes. Obviously, even then if you lock cells, then the user can not interact with them.
Code: 
Sheet1.Protect "YourPasswordHereOrBlankQuotes", UserInterfaceOnly:=True
Click to expand...

Does it matter WHERE I insert this code in the code for each sheet?
 
NARAYANK991 said:
Hi ,

The limit in this forum for file sizes is 1 MB. Clearly , you cannot upload your workbook here.

What you can do is use any public file sharing site such as DropBox , upload your file there , create a share link which gives others permission to access , download and open your file , and then post that link in this thread.

Narayan
Click to expand...
Thanks! So far, by toggling freeze/unfreeze, the dropdown lists appear to work properly on each sheet. Not sure why....
 
Change password ken to suit. Copy and paste to ThisWorkbook.
Code: 
Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data.
  Next ws
End Sub
 
Ken, thank you for all your help. I was hoping to Protect worksheets and contents of locked cells. Your code above is for protecting the workbook?
 
No.

For Each, loops through all Worksheets. It is in ThisWorkbook object so that it can run the loop when the workbook's Open event occurs.
 
You must log in or register to reply here.
Back
Top