Posts by jonny

?

If function, not VBA

By "if" i meant to existing Excel function. As it's less readable, I've got lost there..

The below VBA code scores supplier, based on its response time, please your help to convert it to "IF" function format.

Private Sub Score()
'Scoring Supplier
    Response_Time = Today() - Completed_Date
    Select Case True
        Case Response_Time <= 7
            Score = 4
        Case Response_Time > 7 And Response_Time <= 14
            Score = 3
        Case Response_Time > 14 And Response_Time <= 21
            Score = 2
        Case Response_Time > 21
            Score = 1
    End Select
End Sub

Thank you

Thank you!

Guys,
Need to extract all the letters within the string , separated by comma.
Now the code extracts first letters only.
Please your help with modifying an user defined function:

Function ExtractLetter(str As String)
    Dim TDCode As String
    Dim result As String
    
    On Error GoTo ErrHandler:
    With CreateObject("vbscript.regexp")
        .Pattern = "[A-Z]+"
        ExtractLetter = .Execute(str)(2)
    End With
    Exit Function
ErrHandler:
End Function

Input: HKJ234CBV546LL
Output (is): HKJ
Output (ought): HKJ,CBV,LL

Guys, Need to extract all the letters within the string , separated by comma. Now the code extracts first letters only. Please your help with modifying an user defined function:

Function ExtractLetter(str As String)     Dim TDCode As String     Dim result As String          On Error GoTo ErrHandler:     With CreateObject("vbscript.regexp")         .Pattern = "[A-Z]+"         ExtractLetter = .Execute(str)(2)     End With     Exit Function ErrHandler: End Function

Input: HKJ234CBV546LL Output (is): HKJ Output (ought): HKJ,CBV,LL

Manually is not an issue , I wish to run a script that will run across all my macros and put them on ribbon.

Guys, Is there a way to run over tens of macros within the personal.xls and to create a ribbon with all of them?

Thank you , Guys, "new userform's Initialize event" was as well the solution!

I've created a new form:

Private Sub UserForm_Initialize()
    Test.Show
End Sub

Where the event of the old form "Test" changed from Initialize to "Activate", this did the job.

Private Sub Test_Activate()
    Dim NewCheckBox As MSForms.CheckBox
    Dim X As Integer
    
    'Create the User Form
    With Me
        .Caption = "Test"
        .Width = 450
        .Height = 300
    End With


    For X = 1 To 10
        Set NewCheckBox = Me.Controls.Add("Forms.checkbox.1")
        With NewCheckBox
            .Name = "NewCheckBox" & X + 1
            .Caption = X
            .Value = False
            .Top = 20 + (12 * X)
            .Left = 250
            .Width = 100
        End With
        ReDim Preserve chkArray(1 To X)
        Set chkArray(X).myCheckBox = NewCheckBox
    Next X
End Sub

Hi there,
I would appreciate some assistance to the following problem. The below code populates 10 checkboxes on the form. Clicking on one of them must show up the name of a clicked checkbox however this doesn't happen.
Is there anything wrong with the code?
Assistance would be greatly appreciated.
Thanks

Module

Option Explicit


Dim chkArray() As New clsCheck


Private Sub Test()
    Dim NewCheckBox As MSForms.CheckBox
    Dim NewLable As MSForms.Label
    Dim NewTextBox As MSForms.TextBox
    Dim PartType, TempForm
    Dim X As Integer
    
    Application.VBE.MainWindow.Visible = False
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)


    'Create the User Form
    With TempForm
        .Properties("Caption") = "Test"
        .Properties("Width") = 450
        .Properties("Height") = 300
    End With


    For X = 1 To 10
        Set NewCheckBox = TempForm.designer.Controls.Add("Forms.checkbox.1")
        With NewCheckBox
            .Name = "NewCheckBox" & X + 1
            .Caption = X
            .Value = False
            .Top = 20 + (12 * X)
            .Left = 250
            .Width = 100
        End With
        ReDim Preserve chkArray(1 To X)
        Set chkArray(X).myCheckBox = NewCheckBox
    Next X


        'Show the form
    VBA.UserForms.Add(TempForm.Name).Show
End Sub

Class Module

Public WithEvents myCheckBox As MSForms.CheckBox


Private Sub myCheckBox_Click()
    MsgBox myCheckBox.Caption
End Sub

Many thanks, will check this out..

Just create a new UserForm1 which would replace an existing UserForm1

OzMVP, thank you fo a detailed clarification. I understand it theoretically and have been trying to implement that last days however getting lost with the syntax.

Sorry, I don't see any correlation to what I asked.
I have a code with a dynamically added controls so I'm looking to create a kind of event handler with a right syntax.

[h=2][SIZE=12px]Guys, please your help to modify the code, so I will be able to know the number of checked checkbox.[/SIZE][/h]
[SIZE=12px]Thanks[/SIZE]

Guys, please your help to modify the code, so I will be able to know the number of checked checkbox.

Thanks

Need to Implement communication platform between two users (GUI):

Task and Structure:

1. There are "Parts"
2. For each Part there are "Parameters"
3. Parameter have properties as : Units, Description, Mandatory, Operator, Range

User1 (ask):
1. Selecting specific "Part"
2. Then filling the values for all its "Parameters"
4. Is "Mandatory" selected as "Yes" , then selecting "Operator" and "Range"
5. Next step is to submit the form , submitted form with a part and filled parameters is transmitted to User2

User2 (answer):
1. offering up to three solutions:
(a) for each Paramеters adding his parameter
(b) free text - "Solution 1", "Solution 2", "Solution 3"
(c) attaching PDF format file
2. Next step is to submit the form will all the data back to User1 for review

Admin: can add, delete, modify:
(a) Parts
(b) Parameters with the properties
(c) Users

How to determine in VBA the name of the checked checkbox if there are many?

Great!
How you wish to proceed?