Posts by KjBox

    Re: Macro to Write Code

    One thing I do notice is that the code you have to write the new code does not write the 'End Sub'. It stops at 'End With' This could be the cause of the crashes.

    Re: Running Macros On Protected Sheets

    Hi reafidy,

    Just to let you know problem is now sorted. Though the code was there in the new workbook after the 'Save As' it was not firing because the new workbook had not been properly opened. Adding code to close and reopen the new workbook immediately after the Save As cured it.

    Thanks for your time and help.

    Re: Code To Write Code For Workbook Open Event

    Thank you xlite.

    That works just fine.

    Only problem is that I have to set the reference each time I open the originating workbook. Do you know how I can set it up so that the 'VBA Extensibility Libaries' is checked when I open the originating workbook (as vaious other references are)

    Thanks again.

    Re: Running Macros On Protected Sheets


    Sorry for delay in getting back to you.

    My sequence of events is:

    Workbook A uses 'Save As' to create Workbook B

    (the auto_open code is in Wb A and so transfers to Wb B, the passwords for all sheets in both workbooks are obviously the same. The auto_open code works in wb A except for the workbook protection bit, I cannot get UserInterfaceOnly to work there, get compile error)

    The first page of wb B is an instruction sheet with a command button for user to click in order to continue.

    This is where the auto_open fails. the code for the button is:

    It fails on the first line after Application,ScreenUpdating.

    If I manually unprotect the sheets it works fine.

    Hope this helps with a possible solution.

    Re: Running Macros On Protected Sheets

    Hi Reafidy,

    I know the code is good because I use it in other workbokks successfully but here it will not work.

    I only have the workbook concerned open. Tried the change to ThisWorkbook but still no joy.

    The first macro involves 2 veryhidden sheets. I tried making these hidden and then tried them visible, still no joy. Tried unprotecting the workbook, leaving just sheets protected, still won't work.

    I am right out of ideas as to what could be causing this.

    The error message I get is: Run-time error '1004' and the message: " The cell or chart you are trying to change is protected therefore read only"

    Any help greatly appreciated as this is pushing me towards insanity!!

    P.S. Before somebody suggests otherwise: Yes I have got the password correct
    and yes all macros work when sheets and workbook unprotected!!!!

    I have the following code as workbook module but it is not working, I have tried fiddling with it but I cannot work out what is wrong, can anyone help please.


    Re: Limit One Cell To 55 Characters?

    The code must be placed in the sheet module not a general module.

    Right click the sheet tab for the sheet which has the cells where you want to limit charters, select view code and paste Roy's code to there. The code will run whenever a change is made to columns A or B in that page.

    Re: Enabling A Command Button

    Hi Roy,

    Yes I realised that. What I was trying to get at was:

    The code I posted above worked a couple of days ago then today would not work! The same code in different userforms still works. I was asking if this is a known problem and if so should I change the code in all other userforms to fire on 'change' rather than 'click'.

    Re: Enabling A Command Button

    Thanks Roy,

    That works perfectly.

    The thing that cofuses me is that the original code works on another userform with List boxes and Comman buttons. I have read elsewhere in this forum that .Enabled = True (on its own as I had it) is 'flakey', is it best not to use it alone but to always qualify it as you did?

    I have a userform with, amongst other controls, a ListBox1 and a CommandButton1.

    I have set the CommandButton1 to enabled = False in the properties and need it to be enabled when at least 1 item in the ListBox is selected (the ListBox is multiselect).

    I have the following code, but it is not working.

    Private Sub ListBox1_Click()
        CommandButton1.Enabled = True
    End Sub

    Both controls are in the same userform and the names correct.

    Any ideas?

    Re: Sizing And Positioning Of Shapes

    Thanks yet again Andy. (I think I ought to write a macro to type that automatically!!!).

    I was trying to use Row Height not ActiveCell.Height, works perfwctly now. : D


    I have a workbook where each name in a list has a picture of a flag of the country for that name. The flags are initially assigned to a cell with the country name (see sample attached). Later in the project the flag is copied to a cell in another sheet and assigned to the correct name in the list. The row heights in this list of names is larger than the row height of the sheet with list of countries.

    The flags in the sample have been manually sized and positioned as accurately as possible. I am struggling with the VBA code to size and position each flag exactly so that there is a very small gap between the top, bottom and left of the flag and the cell border. Each is set to move and resize with cell.

    Would greatly appreciate any help.


    Re: Loop Through Pictures And Change Name

    Thanks Andy, I feared as much.

    Trouble is, the names are not set by me but by the individuals themselves. Looks like people are going to have to change their id lol.

    Thanks for your help yet again.