Posts by jl2509

    Hi Carim

    Thanks for the repsonse

    I have tried the above proposal, but do not have anything to add after this to make the commndbutton align next to the activecell.
    Is this possible as I can find nothing in searches.

    btw, is something wrong with the site, I keep getting connection erros and very slow?

    Thank you

    Hi Folks,

    Still building my sheet and yet another question.
    Any thoughts how to align a commandbutton to the left of an active cell?

    The Commandbutton inserts a row above the activecell, but as the sheet is many rows downs, the user would need to scroll up to click the commend button.
    Freezing windows is not an option due the the makeup of the sheet.

    I am currently trying the following but its not ideal as the command button could still be better aligned:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
            On Error GoTo 0
            With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
                CommandButton2.Top = .Top + 200
                CommandButton2.Left = .Left + 10
            End With
    End Sub


    Hi Mumps

    Thanks for putting up with me on this thread.

    I have put something together that may not look pretty, but using snippets from your macrocs and other sources, I think I have something.

    Updated sheet attached with this implemented

    Great posts and great support.

    Thank you

    Hi Mumps

    Sorry, again but am I overcooking this or just rubbish at explaining..

    The only thing I need to happen when I select an option button is to hide or unhide columns. as per the original sheet supplied. I do not need message boxes or print actions at this point.
    The code I need when clicking the printbutton "commandbutton1" is to check the optionbuttons to see what is selected
    then the correct message box with input options then either print or exit the sub

    Many Thanks

    Hi Mumps,

    I really do appreciate your efforts. I thought attached sheet earlier in the thread contained sufdficient detail and does contain all macros except for the print routine that follows the "Checking of Option buttons".

    The reason for the optionbutton macros is because they are continually selected through compilation, and I do not want to print the page until the user is prompted to check the selection for accuracy
    Once the selection is validated, the print routin should continue, which saves to PDF and emails out, hence I dont want the wrong format being sent out by accident

    The 3rd optionbutton is there because of page formatting "Size", as the page is setup to print only one of the columns. Also printing both columns is not required ever.

    I hope this is clear

    Thank you

    Hi Mumps
    Appreciate the response

    Sorry, but I may have confused things,

    The macro does not hide the columns, it only checks which option button is selected then repsonds with the message boxes.

    The columns are hidden by selecting the option buttons.

    As I mentioned, a print routine already exists, I just need to check which optionbutton is selected then run return the correct msgbox with options as per the original sample sheet requirements.

    If this cannot be acheived, I will have to rethink my options.


    Hi and thanks for the repsonse.

    I actully need this running from the command button Mumps,
    I have other macros that run on the onclick events of the option buttons

    The commend button is a print option. I already have the print code in place but need the optionbutton selections to be checked first

    I do need option 3 as the page size is formatted to print only 1 of the columns and also, should never print both columns.

    Thank you

    Hi All,

    I have 3 option butoons on a sheet which hide, resize and add columns to a worksheet

    What I would like to achieve is the following:

    If option button 1 is checked then pop up a yes/no msgbox to say "Option 1 selected, is this correct?",
    if yes then continue code
    if no end sub

    If option button 2 is checked then pop up a yes/no msgbox to say "Option 2 selected, is this correct?",
    if yes then continue code
    if no end sub

    If option button 3 is checked then pop up a vbokonly msgbox to say "You Cannot print this option",
    end sub

    Can anyone help with this please

    Thank you

    Hi Carim

    Thats great, thank you for the follow up.
    I modified the solution slightly as the same columns in the sample sheet are replicated accross the columns many times but with 1 column spacing between.
    so, the rows and columns calculations have moved, but nevertheless an awesome solution

    btw, that is some Array formula, and I have no idea what is means :duh:

    Amazing support once again.

    Thank you

    Hi Carim

    Your time and efforts are very much appreciated
    It is indeed the last version as everything works great.

    I just need to incorporate the extra 2 columns into the worksheet now.
    Should be just a case of moving stuff around, but array formulas can be awkward.

    Thank you

    for further clarity, and if its simpler, the calculations in column D do not need to be for each row.
    This can be a single calculation in D18 to show achievement of all the criteria.


    Hi Carim,

    In my haste to get a result, I omitted a vital criteria from this calculation.

    "y" must be present over the whole range Act 1, Act 2, Act 3 as a minimum but can include Act 4 and Act 5 if they are active.

    In the example I have inclued a sum column to show my thought process, but I cannot include this on the actual worksheet (no columns between the data sets)
    But if it is required, I could change a few things around.

    See attached which hopefully exaplains