Posts by jl2509

Hi All,

Anyone expieriencing slow or timed out connections.
Tried this on multiple different systems and 3 different networks and ozgrid.com is the only site where I get this issue

Tracert shows no connection issues and I see that AliG had a similar issue some time ago?
https://www.ozgrid.com/forum/f…zgrid-excruciatingly-slow

Strange how it clears up works ok for a while, then falls down again...

Thanks

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

Thanks

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.

Private Sub CommandButton1_Click()


Dim Opt1 As String
Dim Opt2 As String
Dim Opt3 As String


Opt1 = "Option 1 selected. Are you sure you want to print these results?"
Opt2 = "Option 2 selected. Are you sure you want to print these results?"
Opt3 = "You cannot print both sets of results. Select OptionButton1 or OptionButton2"


If OptionButton1 = True Then
If MsgBox(Opt1, vbYesNo) = vbNo Then Exit Sub
MsgBox ("Print rountine runs") 'PrintAR
End If


If OptionButton2 = True Then
If MsgBox(Opt2, vbYesNo) = vbNo Then Exit Sub
MsgBox ("Print rountine runs") 'PrintAR
End If


If OptionButton3 = True Then
If MsgBox(Opt3, vbOKOnly) = vbOK Then
Exit Sub


End If
End If
End Sub


Private Sub OptionButton1_Click()
Application.ScreenUpdating = False
    Columns("I").Select
    Selection.EntireColumn.Hidden = True
    Columns("H").Select
    Selection.EntireColumn.Hidden = False
    Application.ScreenUpdating = True
End Sub


Private Sub OptionButton2_Click()
Application.ScreenUpdating = False
    Columns("I").Select
    Selection.EntireColumn.Hidden = False
    Columns("H").Select
    Selection.EntireColumn.Hidden = True
    Application.ScreenUpdating = True
End Sub


Private Sub OptionButton3_Click()
Application.ScreenUpdating = False
    Columns("H").Select
    Selection.EntireColumn.Hidden = False
    Columns("I").Select
    Selection.EntireColumn.Hidden = False
    Application.ScreenUpdating = True
End Sub

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.

Thanks

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 Mumps,

Hope this helps.
Sorry for not uploading a sample earlier, but I didnt have one. Just a sample of what I need but should be ok to modify afterwards.

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.

Thanks

Hi Carim,

Sorry I have really confused you now.

Attached is an updated sampe to hopefully explain a little more.

Thanks

Hi Carim

Thanks for the assistance "Again"

Almost there, but I do not want a sum of the row, just a 1 in the left column if all the criteria is acheived.

Thanks

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

Thanks

Hi Carim

Justthe job, great work.

Quote from Carim

You seem to be surrounded by many challenges ...!!!

I think this is due to age, but cannot be sure :cheers:

Many thanks

Hi Carim

Almost there but I dont want a sum, just a value of 1 returned if a "y" exists anywhere in the range
with of course the limitations of NA being present in H17 and or I17

Updated sheet attached with a few options to show the variables and expected results.

Thanks

Hi Carim,

slipping somewhat eh
Other code was interdependent, but I had an error in a loop which locked the range before other macros coul run.
Fixed now though.

thanks for the consideration though

Hi All,

Sorry if anyone was working on this.

I managed to figure this one out in the end.

Thanks