• 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.

Why wont Design Mode turn off?

willheap

Member
I have taken my first steps into VBA and and with my first attempt I've come up with a problem.


My workbook is sheet and workbook protected (with column formatting allowed) to stop the numerous users destroying my formulas, formatting and print setup when entering data.


Users select from a dropdown list to enter data but some list items are too long to be read so I added a toggle button to temporarily expand the column then the list items can easily be read. Un-toggling returns the column to the original width.


The only VBA that I have implemented is a toggle box in the header row of column D which is linked to this code;


Private Sub ToggleButton1_Click()


If ToggleButton1.Value = True Then

'This area contains the things you want to happen

'when the toggle button is not depressed

Columns("D:D").ColumnWidth = 120


Else

'This area contains the things you want to happen

'when the toggle button is depressed

Columns("D:D").ColumnWidth = 40

End If


End Sub


Design mode can be switched on and off by me when everything is password protected and the toggle button works fine but it seems that once I send this to someone else they can no longer change Design Mode to off even though our security settings are the same. This means that the toggle button doesn't work.


Can anyone suggest what is stopping Design Mode from being turned off or better still, a better way to expand/contract columns to make dropdown lists readable?


Thanks :)
 
Thanks Narayank991 but, unfortunately, I don't think so. The data in column D is text wrapped so once selected from the drop down list it fits the cell but the text in the dropdown list is too wide to be read until the column is expanded. The column needs to be the reduced width for the printed page to be readable.


I didn't think the column widths were the problem? Is this the source of it?


I'm a bit confused as to why it works perfectly on my computer but not on others with identical settings.
 
Hi, willheap!

Could you please upload the file to test it? Just assign a dummy password for this purpose.

Regards!
 
Hi, william!

Here's the link with the guidelines.

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!
 
hmmm, it's not so easy to strip the sensetive bits out of this workbook without destroying the workings. My description of the workbook has been simplified drastically to ease the explaination of the problem...


I will post the link tomorrow when I have checked the stripped down version very carefully ;-)


Thanks guys,


William
 
Hi William ,


Have you checked out the following link , which suggests that increasing the zoom value is a better option.


http://www.contextures.com/xldataval08.html


http://www.mrexcel.com/archive/Data/10574.html


Narayan
 
Thanks Narayank991, I used that method a few years ago and I thought it seemed to be the only option at the time but the users did not like it at all. Their preference is this column-width-toggle button, which would be fine if Design Mode would turn off.


I'm working on a version of the workbook that can be posted here atm. Brb
 
Below is the link to the doc. Not sure if I should post the password to open it here too?


https://docs.google.com/open?id=0B3ihjwO0b6QWODI1MWM4ZGYtZmY1Yy00MzkyLTljMmUtMDQzYTJiNWM1M2Rj


The document is slightly more complicated than first described. Users make selections from drop down lists progressively under Asset, Group, Component and Element to find the required selection. The original list of elements has several thousand items in it to select from but I have taken most of them out for this example.


The dark green box in the green header row is the toggle button for each column.


If you needs more explaination on this workbook then ask me. But, I would think that anyone who is able to help will be able to suss it out easily anyway.
 
Hi, William!

I downloaded your file, but it's password protected, even for opening, and I don't know if for viewing the VBA project or protecting the book structure or worksheets too.

If the passwords are the real ones, I recommend you to delete the uploaded file, open it assigning "1234" to every password in the book, and reupload it.

If you uploaded it with dummy passwords as suggested previously, please post them here, in order to be able to open and analyze it.

Regards!
 
Sorry SirJB7, the password to open the file is;


#20N05%15A01!


(upper-case with zeros not letter "O")


I have already removed the passwords protecting the sheets.


Thanks
 
Hi, William!

I now could open the workbook (a humble advise: with passwords like that is more likely that people who use the file write down the password in a plain text file, even in the desktop! and that's not the idea so... strong passwords are useful, but they're much better if they're easy to remember, just use phrases, street names and numbers, or anything that merges strength with easiness. FYI: nowadays programs that crack passwords -like Elcomsoft for example- just do their job using brute force attacks but not against the password entered by the user -which isn't stored into the file- but against a hash table of 4Gb that comes in a DVD where are stored all the keys possible -transformations/hashes from the plain text passwords entered by the user, used to encrypt the file-, which are significantly less than the permutations of Ascii characters, turning in fact password cracking computational-time possible in a short time... and the result it might be that in minutes you'll be given the password "abc1D" that opens your file too... because both it and the one you entered generate the same hash key. So, don't be afraid, but your file isn't fully secured ... hahaha).

But it works fine for me, just as you sent it with no structure passwords or even protecting sheet "Direct". With sheet protected only happens that the toggle buttons have no effect.

I'm using Excel 2010 and until Monday I can't try it in a computer with Excel 2007.

But I don't think it's a problem with the toggle button behaviour, and I can't figure out which parameter in Excel might cause that malfunction.

For now I can't say anything else, maybe on Monday.

And by the way, I like too much the toggle button than the other older methods ;)

Regards!
 
Hi, William!

Just a little tip: when you have more than one control that perform similar column operations (with different values, of course), you can unify the VBA code behind storing both column widths in this case in Cell <column>1 or <column>1 and <column>2 and then protecting and hiding rows 1 or 1 and 2, and within the code use the associated value to the related column. This will let you reformat your worksheet without modifying code.

Regards!
 
Hi William ,


I downloaded your worksheet ; now what ?


The 4 toggle buttons for GROUP , COMPONENT , ELEMENT and USER DESCRIPTION are all working properly.


Narayan
 
Thank you SirJB7 and Narayank991 for all you assistance.


I have just been informed by the workbook users that it now works fine. No logical reason why it works now but didn't work before because I haven't changed anything yet! They are still using the same workbook. I'll blame it on the Gremlins.


It is reassuring to know that I didn't ruin the workbook with VBA though :)


My next challenge is to work out how to unify the code like SirJB7 suggested. I can understand what you are suggesting but putting it in VBA code is something else...
 
Hi, William!

I was about to write "Tested in Excel 2007 and OK too..." before reading your post.

I modified your workbook with the unified code. I left row 1 without hidding so as you can appreciate the data involved (just both column widths). Give a look at the code for having a comple scope of the changes (just an unified Sub procedure with two parameters: column and button value).

Here's the link:

http://www.2shared.com/file/sm_M7IHU/PEF_example_-_Why_wont_Design_.html

Just advise if any trouble.

Regards!

PS: don't ask me why, but I uploaded the file without password ;)
 
Thanks again SirJB7, I have to admit that I do not understand the VBA code that you wrote - yet! Thanks for taking the time to look at it though, much appreciated.
 
Hi, William!


Let me try to explain it.


1) toggle buttons code

-----

[pre]
Code:
Private Sub ToggleButton1_Click()
ToggleButtonGeneral 4, ToggleButton1.Value
End Sub
-----


All the 4 call a unique procedure passing 2 arguments: first, the column number (4 for D in the example); second, the value of the associated toggle button


2) unified code

-----

Private Sub ToggleButtonGeneral(piColumna As Integer, pbValue As Boolean)
' constants
Const ksSlash = "/"
' declaration
Dim iShort As Integer, iLong As Integer
Dim A As String
' start
' process
'  get widths
A = ActiveWorkbook.ActiveSheet.Cells(1, piColumna).Value
iLong = CInt(Left$(A, InStr(A, ksSlash) - 1))
iShort = CInt(Right$(A, Len(A) - InStr(A, ksSlash)))
'  act on toggling
If pbValue = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
Columns(piColumna).ColumnWidth = iLong
Else
'This area contains the things you want to happen
'when the toggle button is depressed
Columns(piColumna).ColumnWidth = iShort
End If
' end
End Sub
[/pre]
-----


a) ' constants

as a programming habit I don't use constants within the code, instead I define them at the beginning of the procedure (so you avoid reading a line like "if variable="WTF" then..." and you can read "if variable=ConstantForUnknownData then..." ... it's safer and cleaner)


b) ' declarations

in the same way, I use "Option Explicit" as first line in every module, so I'm obliged to define (Dim) the variables I'm gonna use, and I assign an explicit type (Dim A as string, instead of Dim A) in order to assure proper data type ... it's much safer


c) ' start

in this case nothing


d) ' process

the main core part of the code


i.) ' get widths

- first line (A=...) retrieves the value of the first row of the passed argument column number (in the text form "xxx/yyy", where xxx is the width for the long mode and yyy the short one)

- second line (iLong=...) extracts from that string the xxx value (that's to say, the left characters up to "/" -ksSlash constant- position, minus 1)

- third line (iShort=...) idem for the yyy value (right characters from slash position plus 1 to end of string)


ii.) ' act on toggling

the very same code you had before embedded on each click event, but replacing the constant values for the iLong and iShort obtained previously from the column passed as argument


e) ' end

in this case nothing


Hope this'll help you. If not, well, I don't know what to say, but ask again!


Regards!
 
Back
Top