Posts by prsthlm

Re: Displayalerts False Error

Thanks Charis for your support.

Yes, we thought of the same thing. :thanx:

When I sent the "ENTER" after the application.run, then it stopped at the first loop, and then it all passed through the other loops without any alerts.

When I placed the Application.SendKeys "{ENTER}" code before the application.run, it also worked and I also got rid of the first alert.

Best regards
Pontus

Re: Displayalerts False Error

I managed to solve the problem with a send command for the enter key.

With Application
    .SendKeys "{ENTER}"
    .Run "ATPVBAEN.XLAM!Mcorrel", rng, ActiveSheet.Range("$O$8"), "C" _
        , False
End With

Now everything works, thank you!

Re: Displayalerts False Error

Hi and thank you.

The alert I get when running the loop, is from the Mcorrel application saying:
[COLOR="Blue"]"Correlation-Output range will owerwrite existing data. Press OK to overwrite data in range..." [/COLOR]

Is it possible to manage this in any way with the Displayalert or some code sending an OK to the alert?

Hi,

I can't get the Displayalerts = False to work as it's supposed to when analyzing data running the correlation add-in. (ATPVBAEN.XLAM!Mcorrel)

For each loop the alert is displayed which I don't want it to.

The code below is just an example. I have several rows which I will loop through and therefore the Displayalerts=False must work.

Sub test4()


With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
End With
     
Dim rng As Range


For i = 0 To 2


Set rng = Range([b9], [b9].End(xlDown).End(xlToRight))


     Application.Run "ATPVBAEN.XLAM!Mcorrel", rng, ActiveSheet.Range("$O$8"), "C" _
        , False
        
Next i


With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
End With


End Sub

Kind Regards
Pontus

Re: CHART with Checkbox to hide series

Thanks Andy !

I'm glad that I found your great solution at this thread !

Cheers
prsthlm

Quote from Andy Pope

Hi Stu,

Easiest way is to create another set of charting data that is dependent on the checkbox settings and the original data. These formula and figures can then be covered by the chart.

Re: Update Textbox In Parent Frame

Great, Thanks Andy !!!

Now I'm more than happy !
Also thanks for a great explanation !

Best Regards
prsthlm

Hi Andy or anyone else who can help me with this.

The code below works really great which Andy helped me with, but now I've put the textboxes that are changing in a frame. That is because I need to scroll the textboxes.
That works in another form I've got, but for this form I've also got the textbox, TBSum601, which is updated from the sub UpdateTotal() (see below).
TBSum601 is placed on the form but not inside the frame.
When I run the application it stops in Sub TxtGroup_Change() at the line:
TxtGroup.Parent.UpdateTotal
I think it's because of that the textboxes for the TxtGroup now are in a frame instead of as before, on the form.
don't know exactly what the "Parent" does, but I think it's something I have to do with the code there.

Please help me, cheers !
prsthlm

Quote

Originally Posted by Andy Pope
Class event
VBA:

Code

Private Sub TxtGroup_Change() 


    If Me.TxtGroup.Text = "" Then
    Me.TxtGroup.Text = 0
    End If


' [B]Here it fails[/B]
TxtGroup.Parent.UpdateTotal


End Sub




'New routine in userform code
'VBA:


Public Sub UpdateTotal()
Dim lngTotal As Long 
Dim lngIndex As Long 


For lngIndex = 1 To UBound(X) / 4 
' [B]This textbox is now in a frame[/B] 
With Me.Frame1.Controls("TB7" & Format$(lngIndex, "00")) 
  If Len(.Text) = 0 
    Then ' do nothing 
    ElseIf IsNumeric(.Text) Then lngTotal = lngTotal + CLng(.Text) 
    Else ' do nothing 
  End If 
End With 
Next 
' [B]This textbox is not in the frame [/B]
Me.Controls("TBSum601").Value = CStr(lngTotal) 
End Sub

Display More

Re: Hide & Show Scroll Bars For Frame Control

Quote from Dave Hawley

Edit. Ok, we posted at the same time

Yeah, I'm glad that I had the same solution as a pro ! : D

Cheers
prsthlm

Re: Hide & Show Scroll Bars For Frame Control

I made it !
I adjusted the scrollwidth of the frame.
For the application I will change the "2" in 2 * Frame1.Width with a variable to get it to work as I want.

Frame1.ScrollBars = fmScrollBarsHorizontal
Frame1.ScrollWidth = 2 * Frame1.Width

Edit: Yeah, we came up with the same solution. Great ! Thanks !

Cheers
prsthlm

Re: Hide & Show Scroll Bars For Frame Control

Sorry but that didn't make it much more easier for me. :confused:

Quote

Controls within the Frame don't scroll with the Frame as that would defeat their purpose.

How can I get the controls to scroll along with the scrollbar ?

It's a dynamic form so the width of the form varies.
It's 3 rows of controls in the form and the number of columns within the form can be up to 30. At most I just want 10 columns of controls to be seen.

Is this possible ?

Re: Hide & Show Scroll Bars For Frame Control

Quote from Dave Hawley

Excel will detect when they are needed to allow scrolling

Strange... I don't get that to work.
The scrollbar is just there doing nothing.

I tried again to manually add the scrollbar and put min = 1, max = 10
Then the scrollbar works but the textboxes don't follow the scrollbar, they just do "nothing"

What am I doing wrong ?

Maybe this should be another thread title again ?...

Re: Hide & Show Scroll Bars For Frame Control

Thanks for both answers !
I just added the code to the end of the initialize event. I took away the scrollbar that I manually added to the frame

Frame1.ScrollBars = fmScrollBarsNone

I haven't worked with scrollbars in forms before so another question is, do I have to put any more code to get the scrollbar working ?
Now it's just there doing nothing... Surely there is some code to write and I would appreciate some help with that as well

Cheers
prsthlm

Re: When Clicking Close Button 2nd Form Still Visible

Consider this now solved !!!

I found this in my code, not very easy for you, Joe, to know...

In the beginning of my application I had the Application.ScreenUpdating = False

I had to put it back to True in the end of the code, for the form B initialize event.

Best regards
prsthlm

Re: When Clicking Close Button 2nd Form Still Visible

By that I mean that the two forms will be positioned side by side and that works fine. Form B still exists though beside form A, when cancel in form B is clicked and it disappears only after clicking cancel in form A

I hope this made it more easy for you to understand.

Cheers

Re: When Clicking Close Button 2nd Form Still Visible

Thanks Joe for keep trying !

Unfortunately your great idea did not work...

Cheers
prsthlm

Hi,

I have a form that is dynamically created at runtime. I have assigned the controls to a frame in VBA and also attached a horizontal scrollbar manually to that frame.

How can I control the scrollbar in VBA to appear, when columns of controls added to the frame exceeds 10. If not I want the scrollbar to be invisible?

Please give me a helping hand.

Cheers
prsthlm

Private Sub UserForm_Initialize()   
   Const cTextBoxHeight As Long = 16
   Const cTextBoxWidth As Long = 40
   Const cGap As Long = 10
   Dim W As Integer
   Dim a As Integer
    
    Y = 0
    i = 1
    l = 90


' Sim() is a global variable and the look of it is like this:
' "0647", 53, 47, "0648", 55, 48, "0649", 65, 59, ... etc
' Each column in the form will be 3 rows of controls


    For i = 1 To UBound(Sim) / 3
    With Me.Frame1.Controls.Add("Forms.TextBox.1", "TB" & i, True)
            .Value = Format$(Sim(Y), "0000")
            .Left = l
            .Top = 12
            .Height = cTextBoxHeight
            .Width = cTextBoxWidth
            .Locked = True
            .Enabled = False
        End With
        Y = Y + 1
        With Me.Frame1.Controls.Add("Forms.TextBox.1", "TB" & i, True)
            .Value = Sim(Y)
            .Left = l
            .Top = 12 + cTextBoxHeight + cGap
            .Height = cTextBoxHeight
            .Width = cTextBoxWidth
            .Locked = True
            .Enabled = False
        End With
        Y = Y + 1
        With Me.Frame1.Controls.Add("Forms.TextBox.1", "TB" & i, True)
            .Value = Sim(Y)
            .Left = l
            .Top = 12 + cTextBoxHeight * 2 + cGap * 2
            .Height = cTextBoxHeight
            .Width = cTextBoxWidth
            .Locked = True
            .Enabled = False
        End With
        Y = Y + 1
        l = l + cTextBoxWidth + cGap
    Next i
   
End Sub

Re: Assign A Control To A Frame At Runtime

I managed to solve the problem.
To assign the control to a frame I changed the code like this:

Me.Frame1.Controls.Add("Forms.TextBox.1", "TB1", True)

Now I want a scrollbar to appear automatically and if I get a problem with that I start a new thread.

A bit later...
I have a problem with the scrollbar, please take a look at this new thread

Cheers
prsthlm

Hello,

I have a form that is dynamically created at runtime.
Because of this the width of the form can vary.

If the number of controls and the total width of them, exceed the width of the form, I want a scrollbar to appear.
To get this working I need to asign these added controls to a frame which I read in an article.

Quote

one way to do this is to put a Frame on the form, and add the controls to the Frame, rather than to the Form.
A vertical scroll bar comes along with a Frame automatically

The problem is that I don't know how...

How do I assign controls to a frame at runtime ?

'The code now for adding a control
Me.Controls.Add("Forms.TextBox.1", "TB1", True)

Re: When Clicking Close Button 2nd Form Still Visible

Quote from Joe Derr

Souldn't that frm_A.Show be frm_B.show ?

Yes, that's correct. I just didn't edit the whole code when editing here.

So the problem still exists, with the B form still visible after clicking the cancel button.
In this application the B form gets it's values from an global array created in Form A.

Here's the correct code:

Private Sub cmd_RunA_Click() 
    'Button at worksheet
    Dim PS As Positions 
    frm_A.StartupPosition = 0 
    PS = PositionForm(frm_A, ActiveSheet.Range("A20"), 0, 0, cstFhpAuto, cstFvpAuto) 
    frm_A.Top = PS.FrmTop 
    frm_A.Left = PS.FrmLeft 
     
    frm_A.Show 
End Sub 
 
Private Sub cmd_ok_Click() 
     'Within form A
    Dim PS As Positions 
    frm_B.StartupPosition = 0 
    PS = PositionForm(frm_B, ActiveSheet.Range("G20"), 0, 0, cstFhpAuto, cstFvpAuto) 
    frm_B.Top = PS.FrmTop 
    frm_B.Left = PS.FrmLeft 
     
    frm_B.Show 
End Sub 


Private Sub cmd_cancel_Click() 
    'Within form B
    Unload Me 
End Sub

Re: When Clicking Close Button 2nd Form Still Visible

Quote from royUK

Why not simply use one form with a Multipage Control?

I had a look at this and no, that's not what I want.
I want the two forms.

Cheers
prsthlm