Fill in a dynamic created textbox with text a combination of checkbox and option buttons selected.

M

madmiddle

New Member
Joined
Mar 8, 2012
Messages
45
Once again I bow to the superior knowledge of the group to see if I what i want to do can be done.

I have a user from that is the first part of a mass email script that I am trying to write. What I would like is select one or several checkboxes in the first frame and then select 1 optionbutton in frame 2 and then that will populate one or several textboxes.

Has anybody done this before ? or have I stretched my limited to intermediate knowledge of visual basic too far ?

Capture.jpg.html
Capture.jpg.html
Capture.jpg.html
<a href="http://s1148.photobucket.com/user/madmiddle1/media/Capture.jpg.html" target="_blank"><img src="https://oi1148.photobucket.com/albums/o565/madmiddle1/th_Capture.jpg" border="0" alt=" photo Capture.jpg" style=""></a>
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
To help you, you need to describe the different cases, that is:
- If you select which or which checkbox and also select which optionbutton, what data and in which textbox it goes.
 
Upvote 0
DanteAmor said:
To help you, you need to describe the different cases, that is:
- If you select which or which checkbox and also select which optionbutton, what data and in which textbox it goes.
Click to expand...

Yeah that would help.

Scenario 1: Only 1 of the check boxes is ticked and an option button is selected and then in frame 7 at the bottom of the userform a textbox is dynamically created with a pre-detemined text in it.

App 1 - Planned Outage - [App1 Planned Outage Text]

Scenerio 2: Several (lets say 3) of the checkboxes are ticked and again an optionbutton is selected and then in frame 7 at the bottom 3 textboxes are dynamically added.

App 1 - Planned Outage - [App1 Planned Outage Text]
App 2 - Planned Outage - [App2 Planned Outage Text]
App 3 - Planned Outage - [App3 Planned Outage Text]
 
Last edited:
Upvote 0
DanteAmor said:
To help you, you need to describe the different cases, that is:
- If you select which or which checkbox and also select which optionbutton, what data and in which textbox it goes.
Click to expand...

Here is the code i have so far but it works off the the checkbox clicks rather than include the optionbuttons to detemine what text should go into the created text box.

This is what is in the userform
Code: 
Option ExplicitPrivate WithEvents cControls As EventController


Private Sub cancel_button_Click()
    Unload Me
End Sub


Private Sub cControls_Click(ctrl As CheckBoxControl)
    Dim tBox As TextBoxControl
    Dim i As Long
    Dim NextTop As Long, FrameHeight As Long


    For i = 1 To cControls.GetControls.Count
        'Debug.Print TypeName(cControls.GetControl(i))
        If TypeName(cControls.GetControl(i)) = "TextBoxControl" Then
            Set tBox = cControls.GetControl(i)
            If tBox.TXT.Parent Is Me.Frame7 Then
                NextTop = tBox.Top + tBox.Height
            End If
        End If
    Next i


    Set tBox = cControls.AddTextBox
    With tBox
        .Height = 125
        .Width = 280
        .Left = 5
        .Top = NextTop
        .TXT.text = ctrl.cBox.Caption
        FrameHeight = NextTop + .Height
    End With
    If FrameHeight > Me.Frame7.InsideHeight Then
        With Me.Frame7
            .ScrollBars = fmScrollBarsVertical
            .ScrollHeight = FrameHeight
            .Scroll yAction:=6
        End With
    End If


End Sub


Private Sub UserForm_Initialize()
    Dim i As Long
    Dim cBox As CheckBoxControl
    Set cControls = New EventController
    ' This can be set to a userform or a frame
    Set cControls.UserForm = Me
    For i = 1 To 8
        Set cBox = cControls.AddCheckBox
        cBox.cBox.Left = 5
        With cBox.cBox
            .Top = 5 + (i - 1) * .Height
            .Caption = IIf(i = 8, "App Unknown", "App " & i)
        End With
    Next i


End Sub


Private Sub cControls_Change(ctrl As TextBoxControl)
    ' This can be handled in the class instead as you were - just doing it in the userform to show the exposing of the event
    MsgBox ctrl.TXT.Name & " Change"
End Sub


Private Sub cControls_SpinDown(ctrl As TextBoxControl)
    ' This can be handled in the class instead as you were - just doing it in the userform to show the exposing of the event
    With ctrl.SP
        If .Value > 0 Then
            .Value = .Value - 1
        End If
    End With
    MsgBox ctrl.SP.Name & " Spin Down"
End Sub


Private Sub cControls_SpinUp(ctrl As TextBoxControl)
' This can be handled in the class instead as you were - just doing it in the userform to show the exposing of the event
    With ctrl.SP
        .Value = .Value + 1
    End With
    MsgBox ctrl.SP.Name & " Spin Up"


End Sub

This is what is in the cControlEvent Class
Code: 
Option Explicit

Public WithEvents SP As MSForms.SpinButton
Public WithEvents TXT As MSForms.TextBox
Private cParent As cCtrl




Private Sub SP_SpinDown()
    SP.Value = SP.Value - 1
    MsgBox "Spin Down to " & SP.Value
End Sub


Private Sub SP_SpinUp()


    SP.Value = SP.Value + 1
    MsgBox "Spin Up to " & SP.Value
End Sub


Private Sub TXT_Change()
    MsgBox "You changed the value."
End Sub






Public Sub Initialize(Parent As cCtrl)
    Set Me.Parent = Parent
    With Parent.UserForm.Controls
        Set TXT = .Add("Forms.TextBox.1")
        Set SP = .Add("Forms.SpinButton.1")
        SP.Left = TXT.Left + TXT.Width
    End With
End Sub
Public Property Set Parent(v As cCtrl)
    Set cParent = v
End Property
Public Property Get Parent() As cCtrl
    Set Parent = cParent
End Property
Public Property Let Left(v As Single)
    TXT.Left = v
    SP.Left = TXT.Left + TXT.Width
End Property
Public Property Get Left() As Single
    Left = TXT.Left
End Property
Public Property Let Top(v As Single)
    TXT.Top = v
    SP.Top = v
End Property
Public Property Get Top() As Single
    Top = TXT.Top
End Property
Public Property Let Height(v As Single)
    TXT.Height = v
    SP.Height = v
End Property
Public Property Get Height() As Single
    Height = TXT.Height
End Property
Public Property Let Width(v As Single)
    TXT.Width = v - SP.Width
    SP.Left = TXT.Left + TXT.Width
End Property
Public Property Get Width() As Single
    Width = TXT.Width + SP.Width
End Property

cCtrl Class
Code: 
Option ExplicitPrivate CtrlCollection As Collection
Private cUserForm As UserForm


Public Event SpinDown(ctrl As cControlEvent)
Public Event SpinUp(ctrl As cControlEvent)
Public Event Change(ctrl As cControlEvent)
Public Property Set UserForm(v As UserForm) ' Used
    Set cUserForm = v
End Property
Public Property Get UserForm() As UserForm
    Set UserForm = cUserForm
End Property
Public Function AddTextBox() As cControlEvent ' Used
    Dim tBox As cControlEvent
    Set tBox = New cControlEvent


    CtrlCollection.Add tBox


    tBox.Initialize Me


    Set AddTextBox = tBox


End Function


Private Sub Class_Initialize() 'Used
    Set CtrlCollection = New Collection
End Sub
Public Sub SpinDown(ctrl As cControlEvent)
    RaiseEvent SpinDown(ctrl)
End Sub
Public Sub SpinUp(ctrl As cControlEvent)
    RaiseEvent SpinUp(ctrl)
End Sub
Public Sub Change(ctrl As cControlEvent)
    RaiseEvent Change(ctrl)
End Sub

CheckBoxControl
Code: 
Option Explicit


Public WithEvents cBox As MSForms.CheckBox
Private cParent As EventController
Public Property Set Parent(v As EventController)
    Set cParent = v
End Property
Public Property Get Parent() As EventController
    Set Parent = cParent
End Property
Public Sub Initalize(Parent As EventController)
    Set Me.Parent = Parent
    Set cBox = Parent.UserForm.Frame1.Controls.Add("Forms.CheckBox.1")
End Sub


Private Sub cBox_Click()
    Parent.Click Me
End Sub

EventController
Code: 
Option Explicit


Private CtrlCollection As Collection
Private cUserForm As email_creationform


Public Event SpinDown(ctrl As TextBoxControl)
Public Event SpinUp(ctrl As TextBoxControl)
Public Event Change(ctrl As TextBoxControl)
Public Event Click(ctrl As CheckBoxControl)
Public Property Set UserForm(v As email_creationform)
    Set cUserForm = v
End Property
Public Property Get UserForm() As email_creationform
    Set UserForm = cUserForm
End Property
Public Function AddTextBox() As TextBoxControl
    Dim tBox As TextBoxControl
    Set tBox = New TextBoxControl


    tBox.Initialize Me


    CtrlCollection.Add tBox


    Set AddTextBox = tBox


End Function
Public Function AddCheckBox() As CheckBoxControl
    Dim cBox As New CheckBoxControl
    cBox.Initalize Me
    CtrlCollection.Add cBox


    Set AddCheckBox = cBox
End Function
Public Function GetControl(Index As Long)
    Set GetControl = CtrlCollection(Index)
End Function
Public Function GetControls() As Collection
    Set GetControls = CtrlCollection
End Function
Private Sub Class_Initialize()
    Set CtrlCollection = New Collection
End Sub
Public Sub SpinDown(ctrl As TextBoxControl)
    RaiseEvent SpinDown(ctrl)
End Sub
Public Sub SpinUp(ctrl As TextBoxControl)
    RaiseEvent SpinUp(ctrl)
End Sub
Public Sub Change(ctrl As TextBoxControl)
    RaiseEvent Change(ctrl)
End Sub
Public Sub Click(ctrl As CheckBoxControl)
    RaiseEvent Click(ctrl)
End Sub

TextBoxControl
Code: 
Option Explicit


Public WithEvents SP As MSForms.SpinButton
Public WithEvents TXT As MSForms.TextBox
Private cParent As EventController
Public Sub Initialize(Parent As EventController)
    Set Me.Parent = Parent
    With Parent.UserForm.Frame7.Controls
        Set SP = .Add("Forms.SpinButton.1")
        Set TXT = .Add("Forms.TextBox.1")
    End With
End Sub
Public Property Set Parent(v As EventController)
    Set cParent = v
End Property
Public Property Get Parent() As EventController
    Set Parent = cParent
End Property
Public Property Let Left(v As Single)
    TXT.Left = v
    SP.Left = TXT.Left + TXT.Width
End Property
Public Property Get Left() As Single
    Left = TXT.Left
End Property
Public Property Let Top(v As Single)
    TXT.Top = v
    SP.Top = v
End Property
Public Property Get Top() As Single
    Top = TXT.Top
End Property
Public Property Let Height(v As Single)
    TXT.Height = v
    SP.Height = v
End Property
Public Property Get Height() As Single
    Height = TXT.Height
End Property
Public Property Let Width(v As Single)
    TXT.Width = v - SP.Width
    SP.Left = TXT.Left + TXT.Width
End Property
Public Property Get Width() As Single
    Width = TXT.Width + SP.Width
End Property


Public Sub SP_SpinDown()
    Parent.SpinDown Me
'    SP.Value = SP.Value - 1
'    MsgBox "Spin Down to " & SP.Value
End Sub
' The commented out lines below you can either leave in here, or handle in the Userform
Public Sub SP_SpinUp()
    Parent.SpinUp Me
'    SP.Value = SP.Value + 1
'    MsgBox "Spin Up to " & SP.Value
End Sub


Public Sub TXT_Change()
    Parent.Change Me
'    MsgBox "You changed the value."
End Sub
 
Upvote 0
madmiddle said:
Scenerio 2: Several (lets say 3) of the checkboxes are ticked and again an optionbutton is selected and then in frame 7 at the bottom 3 textboxes are dynamically added.

App 1 - Planned Outage - [App1 Planned Outage Text]
App 2 - Planned Outage - [App2 Planned Outage Text]
App 3 - Planned Outage - [App3 Planned Outage Text]
Click to expand...

Do you want to create a textbox for each checkbox?
Were the checkboxes dynamically created?


Do you want to continue with this complicated development?


I can think of a couple of simpler alternatives.


For example a single textbox and a button.
You add the text in the textbox and press the button, then you add the text to a listbox.
This way you can add dozens of texts and you only need a textbox.
You can manipulate the data in the listbox to modify, consult or delete.
To manipulate textboxes created automatically, you would have to create algorithms and algorithms to identify which one to delete and which ones to leave.
 
Upvote 0
DanteAmor said:
Do you want to create a textbox for each checkbox?
Were the checkboxes dynamically created?


Do you want to continue with this complicated development?


I can think of a couple of simpler alternatives.


For example a single textbox and a button.
You add the text in the textbox and press the button, then you add the text to a listbox.
This way you can add dozens of texts and you only need a textbox.
You can manipulate the data in the listbox to modify, consult or delete.
To manipulate textboxes created automatically, you would have to create algorithms and algorithms to identify which one to delete and which ones to leave.
Click to expand...

Thank you, I would like to create a text box for every checkbox and optionbutton combination, so if App 1 is checked and planned outage is selected then it would create 1 textbox with pre-defined text into it.

If for example 3 (App 1, App2 & App5) are selected and 1 optionbutton is selected then i would like 3 textboxes creating each with their own pre-determined text.

so Textbox 1 would have App 1 - Planned Outage - [App 1 - Planned Outage + App 1 Planned Outage text in the textbox], App 2 - Planned Outage - [App 2 - Planned Outage + App 2 Planned Outage text in the textbox].

The source of the text is within the workbook itself. and will be saved to the workbook if the text is changed but i'll struggle with that later


So sorry for not explaining it well, due my total lack of understanding of the terminology I'm finding it hard to describe what i want.

AND YES...... if there is a simplier way of achieving this then please lead the way. I was only using this code as it was going slightly in the direction i wanted but not quite.


If i explain the end goal that might help explain it.

I look after several applications and if for example a server goes down then we might lose several applications at a time. so i need the ability to send several different tailored emails to the different users.
The code for the email lists and actually creating the emails has been written and finished, it's just the user friendly front end that i'm struggling with.
 
Last edited:
Upvote 0
DanteAmor said:
Do you want to create a textbox for each checkbox?
Were the checkboxes dynamically created?


Do you want to continue with this complicated development?


I can think of a couple of simpler alternatives.


For example a single textbox and a button.
You add the text in the textbox and press the button, then you add the text to a listbox.
This way you can add dozens of texts and you only need a textbox.
You can manipulate the data in the listbox to modify, consult or delete.
To manipulate textboxes created automatically, you would have to create algorithms and algorithms to identify which one to delete and which ones to leave.
Click to expand...


This is the other code I had working:

Code: 
Private Sub CommandButton3_Click()

    Dim cSpnEvnt As cControlEvent
    Dim ctlSB As Control
    Dim ctlTXT As Control
    Dim lngCounter As Long
    
    For lngCounter = 1 To 5
        Set ctlTXT = Me.Frame7.Controls.Add("Forms.TextBox.1", "Text" & lngCounter)
        With ctlTXT
            .Caption = "Hello " & lngCounter
            .Name = "Text" & lngCounter
            .Left = 5
            .Height = 15: .Width = 50
            .Top = (lngCounter - 1) * 17 + 2
    
        End With
    
        Set ctlSB = Me.Frame7.Controls.Add("Forms.SpinButton.1", "Spin" & lngCounter)
        ctlSB.Name = "Spin" & lngCounter
        ctlSB.Left = 60
        ctlSB.Height = 15: ctlSB.Width = 10
        ctlSB.Top = (lngCounter - 1) * 17 + 2
        
        Set cSpnEvnt = New cControlEvent
        Set cSpnEvnt.SP = ctlSB
        Set cSpnEvnt.TXT = ctlTXT
        SpnColct.Add cSpnEvnt
    
    Next lngCounter
    
    Me.Frame1.ScrollHeight = (lngCounter - 1) * 17 + 2
    
End Sub


Sub userform_Initialize()
    Set SpnColct = New Collection


End Sub

and a class module called cControlEvent with this :

Code: 
Option Explicit

Public WithEvents SP As MSForms.SpinButton
Public WithEvents TXT As MSForms.TextBox


Private Sub SP_SpinDown()
    SP.Value = SP.Value - 1
    MsgBox "Spin Down to " & SP.Value
End Sub


Private Sub SP_SpinUp()
    SP.Value = SP.Value + 1
    MsgBox "Spin Up to " & SP.Value
End Sub


Private Sub TXT_Change()
    MsgBox "You changed the value."
End Sub
 
Upvote 0
If you already have the applications that are on each server, and you have the mailing list of those responsible for each application.
Then you could handle it with combobox.
In combo1 the list of servers.
If you select the XYZ-server, it will automatically show you the list of applications of that server and also the emails, then press a button and send the mail.

Put some examples of what you have on your sheets.
And what would you like to do.
 
Upvote 0
DanteAmor said:
If you already have the applications that are on each server, and you have the mailing list of those responsible for each application.
Then you could handle it with combobox.
In combo1 the list of servers.
If you select the XYZ-server, it will automatically show you the list of applications of that server and also the emails, then press a button and send the mail.

Put some examples of what you have on your sheets.
And what would you like to do.
Click to expand...

That was a route I didn't want to go down as there is times where we would only take a single application offline to update it. wanted to be flexible with the amount of applications I was sending emails out about.
 
Upvote 0
madmiddle said:
That was a route I didn't want to go down as there is times where we would only take a single application offline to update it. wanted to be flexible with the amount of applications I was sending emails out about.
Click to expand...

Let's start with the server.
You select a server.
In a listbox all the applications of that server will be shown, you can select one, several or all applications.
After selecting the applications, the mails appear, you can select one, several or all mails.


If you already have all that in catalogs, it is easier to handle.
No need to create controls automatically.

Another option is to start with applications, you can put a listbox or a combo with the entire list of applications and their respective server.

--------------------
Or what is the objective?
 
Upvote 0
You must log in or register to reply here.

Similar threads

dss28
  • Question Question
autofill cell value based on combined selection of checkbox and option button
Replies
0
Views
764
dss28
dss28
N
  • Question Question
Multi userform validate for textbox,Checkbox,optionbutton is not empty using class module codes
Replies
0
Views
1K
nileshjadhavshiv
N

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top