Converting a successful Excel Spreadsheet to a VBA based user form.

[bralew]

Hi,

I've recently constructed a spreadsheet which makes a simple calculation when a component is selected, It works perfectly
but I've been asked to supply another department. However, to do that its apparent that something in VBA will
be alot more suitable and stable.

So, I've compiled a combo box with a list of items which determines my variance in the calculation.
The Combo box has (currently) 3 variables in the box:
C05093 = which equals 50
C05094 = which equals 53
C05095 = which equals 55

Therefore, the calculation is:

Team Size - C05095 - offset = 1235mm
1300mm - 50mm - 10mm = 1235mm

The variable part is the pull down menu of C05095.

Any advice?

[Olly]

It's not entirely clear exactly what advice you are asking for!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bralew]

The file is on way and been modified for confidentiality. If you enter a team size and change the action it will change the width which determine the recommended outcome, Ignore answer and MBC columns.

Thanks

[bralew]

OK , I'll prep one now

[Olly]

Sorry, I'm still not clear on what you are actually asking for....

[bralew]

I'm trying to determine the VBA code to emulate what the spreadsheet does i.e. - enter team figure, pull down the action menu which will give you a width and that will provide the user with a recommended reading, which the the figure that is important.

[sanram]

Try this:

Private Sub ComboBox1_Change()
    If ComboBox1.Value = "BRCKT032" Then
        TextBox2.Value = 51
    ElseIf ComboBox1.Value = "BRCKT001" Then
        TextBox2.Value = 56
    ElseIf ComboBox1.Value = "530133.003" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT001" Then
        TextBox2.Value = 56
    ElseIf ComboBox1.Value = "C03016" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT148" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "C04014" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT042" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT053" Then
        TextBox2.Value = 55
    ElseIf ComboBox1.Value = "BRCKT046" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT228" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "C07010" Then
        TextBox2.Value = 63.5
    ElseIf ComboBox1.Value = "C05051" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT044" Then
        TextBox2.Value = 63
    ElseIf ComboBox1.Value = "BRCKT208" Then
        TextBox2.Value = 63
    ElseIf ComboBox1.Value = "BRCKT187" Then
        TextBox2.Value = 63
    ElseIf ComboBox1.Value = "C05016" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "C05047" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "C05050" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "C05046" Then
        TextBox2.Value = 58
    ElseIf ComboBox1.Value = "C05001" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT174" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT209" Then
        TextBox2.Value = 63
    ElseIf ComboBox1.Value = "BRCKT163" Then
        TextBox2.Value = 62
    ElseIf ComboBox1.Value = "BRCKT162" Then
        TextBox2.Value = 62
    ElseIf ComboBox1.Value = "BRCKT161" Then
        TextBox2.Value = 62
    ElseIf ComboBox1.Value = "C04069" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT057" Then
        TextBox2.Value = 54.5
    ElseIf ComboBox1.Value = "C04033" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "C04035" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "C04056" Then
        TextBox2.Value = 55
    ElseIf ComboBox1.Value = "C04032" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "C04019" Then
        TextBox2.Value = 55
    ElseIf ComboBox1.Value = "C03015" Then
        TextBox2.Value = 52.5
    ElseIf ComboBox1.Value = "BRCKT233" Then
        TextBox2.Value = 54.5
    ElseIf ComboBox1.Value = "C04015" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT035" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT020" Then
        TextBox2.Value = 52.5
    ElseIf ComboBox1.Value = "BRCKT040" Then
        TextBox2.Value = 20
    ElseIf ComboBox1.Value = "BRCKT236" Then
        TextBox2.Value = 53.5
    ElseIf ComboBox1.Value = "BRCKT242" Then
        TextBox2.Value = 35
    ElseIf ComboBox1.Value = "BRCKT033" Then
        TextBox2.Value = 51
    ElseIf ComboBox1.Value = "BRCKT031" Then
        TextBox2.Value = 55
    ElseIf ComboBox1.Value = "BRCKT016" Then
        TextBox2.Value = 55
    ElseIf ComboBox1.Value = "BRCKT030" Then
        TextBox2.Value = 55
    ElseIf ComboBox1.Value = "530133.002" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "530133.001" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT245" Then
        TextBox2.Value = 34
    ElseIf ComboBox1.Value = "BRCKT237" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT235" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT067" Then
        TextBox2.Value = 60
    ElseIf ComboBox1.Value = "BRCKT027" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT028" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT029" Then
        TextBox2.Value = 50
    ElseIf ComboBox1.Value = "BRCKT004" Then
        TextBox2.Value = 51
    ElseIf ComboBox1.Value = "BRCKT003" Then
        TextBox2.Value = 51
    ElseIf ComboBox1.Value = "BRCKT002" Then
        TextBox2.Value = 56
    ElseIf ComboBox1.Value = "BRCKT148" Then
        TextBox2.Value = 50
    Else
        TextBox2.Value = ""
    End If
End Sub

[bralew]

should i paste in the module or user form?

[sanram]

In UserForm. But I think it would be better if you can declare a global array() for the action values.

[Olly]

I would keep the master data in a worksheet, as it already is, then use something like:

Private Sub UserForm_Initialize()
    Dim c As Range
    With Worksheets("Data")
        For Each c In .Range("W6", .Cells(Rows.Count, "W").End(xlUp))
            Me.ComboBox1.AddItem c.Value
        Next c
    End With
End Sub

Private Sub ComboBox1_Change()
    Dim dWidth As Double
    Dim c As Range
    
    Set c = Worksheets("Data").Range("W:W").Find(what:=Me.ComboBox1.Value, lookat:=xlWhole)
    If Not c Is Nothing Then dWidth = c.Offset(0, 1).Value
    Me.TextBox2.Value = Abs(Me.TextBox1.Value - dWidth - 10)
    
End Sub

It will be much easier to manage modifications to your master data on a worksheet, than embedded in your form code. You can always set the data sheet to be completely hidden, if you want to protect it from your users.

[bralew]

It works great, there's just one other amendment and its ready to go. Rather than display the 55 in the min box, would it be possible to take the following and put the outcome in the min box:
Say, 1300 - BRCKT016 (55) - 10 = 1235, whereby the 1235 is displayed in the Min Box??

[bralew]

So my VBA code now has to take the contents of Team Size minus the min box minus 10 and display the outcome in an extra box called position.

[Olly]

So my VBA code now has to take the contents of Team Size minus the min box minus 10 and display the outcome in an extra box called position.

Did you try my code, in post #10 ?

[bralew]

Sorry scrap that, it's easier to add another text box and use that as the outcome.

[xlnitwit]

I would suggest loading two columns (Action and Width) from the sheet into the combobox control. You can hide one by making its ColumnWidth 0 but then refer to that value directly for the selected item rather than needing to look it up each time.

[bralew]

Sorry Olly, now seeing it. I've decided to refrain from putting any data in the Excel Spreadsheet and use VBA methods exclusively. I was able to design OK with Excel but my VBA is currently limited. I'm going to try and keep every thing in the VBA domain rather than have different data locations.

[Kyle123]

Sorry Olly, now seeing it. I've decided to refrain from putting any data in the Excel Spreadsheet and use VBA methods exclusively. I was able to design OK with Excel but my VBA is currently limited. I'm going to try and keep every thing in the VBA domain rather than have different data locations.

Why? There aren't different domains there is only Excel, all you're doing with VBA is automating it, why would you want to make things more difficult and less flexible?

[Olly]

It's an unusual way of doing it - the worksheet is a PERFECT place for storing a 2 dimensional array of values, such as your bracket list, with corresponding widths.

If you insist, though, then I'd store your master data in a couple of constants. So, in a standard module, enter the following:

Option Explicit

Public Const sBracketArray As String = "BRCKT016|BRCKT148|BRCKT001|BRCKT002|BRCKT003|BRCKT004|BRCKT027|BRCKT028|BRCKT029|BRCKT040|BRCKT067|BRCKT235|BRCKT236|BRCKT237|BRCKT242|BRCKT245|530133.001|530133.002|BRCKT030|BRCKT031|BRCKT032|BRCKT208|BRCKT034|BRCKT046|BRCKT148|BRCKT001|BRCKT002|BRCKT003|BRCKT004|BRCKT027|BRCKT028|C04014|BRCKT040|BRCKT067|BRCKT235|BRCKT236|BRCKT237|BRCKT242|BRCKT245|BRCKT247|530133.001|530133.002|BRCKT030|BRCKT031|BRCKT032|BRCKT033|BRCKT042|BRCKT053|BRCKT058|BRCKT202|BRCKT221|BRCKT217|BRCKT234|BRCKT195|BRCKT197|530133.003|C03016|C04014|BRCKT020|C03015|BRCKT016|BRCKT035|BRCKT233|C04015|C04019|C04032|C04033|C04035|C04056|C04069|BRCKT057|BRCKT161|BRCKT162|BRCKT163|BRCKT174|BRCKT209|C05001|C05016|C05046|C05047|C05050|C05051|BRCKT044|BRCKT187|BRCKT208|BRCKT228|C07010"
Public Const sWidthArray As String = "55|50|56|56|51|51|50|50|50|20|60|50|53.5|60|35|34|50|50|55|55|51|63|51|50|50|56|56|51|51|50|50|50|20|60|50|53.5|60|35|34|40|50|60|55|55|51|51|50|55|50|56|68|80|55|63.5|63.5|60|50|50|52.5|52.5|54.5|60|54.5|60|55|60|60|60|55|60|54.5|62|62|62|60|63|60|60|58|60|60|60|63|63|63|60|63.5"

Sub Button1_Click()
    UserForm1.Show
End Sub

Then in your userform module, enter:

Option Explicit

Private Sub CancelButton_Click()
    Unload Me
End Sub

Private Sub OKButton_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim s() As String
    
    s = Split(sBracketArray, "|")
    
    With Worksheets("Data")
        For i = LBound(s) To UBound(s)
            Me.ComboBox1.AddItem s(i)
        Next i
    End With
End Sub

Private Sub ComboBox1_Change()
    Me.TextBox2.Value = Abs(Me.TextBox1.Value - GetWidth(Me.ComboBox1.Value) - 10)
End Sub

Function GetWidth(ByVal sBracket As String)
    Dim i As Integer
    Dim sB() As String
    Dim sW() As String
    sB = Split(sBracketArray, "|")
    sW = Split(sWidthArray, "|")
    For i = LBound(sB) To UBound(sB)
        If sB(i) = sBracket Then
            GetWidth = CDbl(sW(i))
            Exit Function
        End If
    Next i
End Function

See attachment for a worked example.

[bralew]

Hi,

I,ve tried this code but to no avail. Basically, I want text box 3 to display the results of the sum of text box1 and text box2. Here's the code

Private Sub TextBox3_Change()
Me.Label1 = Me.TextBox1 + Me.TextBox2


End Sub

[Olly]

Have you tried the code (and attached workbook) I posted in post #18 ? Does that not do what you wanted?

[bralew]

Olly, that is wonderful, there's just one small addition. Would it be possible to put the answer in "position" textbox and in width in text box width. I've updated the userform accordingly. But that is brilliant, fairplay!!!!.
I'll attach the file

[bralew]

thanks olly, sorry I'm trying to gets to grips with VBA and keep an eye on the forum, I'll give it a whirl now mate :-)

[bralew]

and put the readings in mm??????????? :-)

[Olly]

Okay, change this section of the code:

Private Sub ComboBox1_Change()
    Dim dWidth As Double
    dWidth = GetWidth(Me.ComboBox1.Value)
    Me.TextBox2.Value = Format(dWidth, "0.0 ""mm""")
    Me.TextBox3.Value = Format(Abs(Me.TextBox1.Value - dWidth - 10), "0 ""mm""")
End Sub

[bralew]

That is beautiful!! Thank you ever so much, I've got plenty to keep my occupied now, Thank You Olly :-)

[Olly]

Happy to help. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[sanram]

As you are trying to learn, so I have attached this file, which will show you another way. I have used 2 dimensional array here.