Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Logic to Button in a Form (Excel 2000)

    I am trying to add logic to a button on a form I have built. Basically the form is designed so a user can select what information they want to input into a selected range.

    Where my issues are:

    before the User click the Decision Input button. they click a cell under I22. and then click the Decision Input button.
    this cell marks the row where the information is loaded in to from the user form.

    In the UserForm I have named checkbox objects:
    TemperatureBox
    FormationBox
    BaseFluid1Box
    TreatmentTypeBox
    CompanyBox

    these check boxes are what the user click on if the condition is to be added

    after the check which objects they want I have named input boxes where they input the data.
    LoTempText and HiTempText
    Formation Text
    BaseFluidText
    TreatmentTypeText
    CompanyText

    The user will fill these in using eithe typing method or picking from the dropdown box.
    after all is filled the user clicks on Fill in.

    this is where I have no Idea what kind of code is need to make this work.
    Basically each check box option addes to a formula.
    so for ex.
    cell I27 is selected
    the Decision Input Button is Clicked
    TemperatureBox is checked
    the user input 100 in LoTempText and 300 in HiTempText
    100 will be added to cell C27
    300 will be added to cell D27

    this formula will be added to I27 =IF(AND($B$3>100,$B$3<300),INDEX($A$8:$A$17,MATCH( $B27,$B$8:$B$17,0)),"")

    If the the formation checkbox is also check and the lock is typed in
    lock will be entered in to cell E27 also
    and the Formula in I27 is now
    =IF(AND($B$3>100,$B$3<300,$B$4=E27),INDEX($A$8:$A$ 17,MATCH($B27,$B$8:$B$17,0)),"")
    does anyone know what type of code Ineed to make a macro which builds on a formula base on whats chosen?

    Thanks for Reading,

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    At the top of your form module I would add string constants for the possible formulae, including specific text for the variables - e.g.:
    <code>mstrTEMP_FORMULA = "=IF(AND($B$3>temp_lo,$B$3<temp_hi),INDEX($A$8:$A$17,MATCH($Brow_num,$B$8:$B$17,0)),"")"</code>
    then in your decision code you can choose which formula you need to use as a base and then just replace the variables - e.g.:
    <pre>strFormula = Replace(mstrTEMP_FORMULA, "temp_lo", Me.LoTempText)
    strFormula = Replace(strFormula, "temp_hi", Me.HiTempText)
    strFormula = Replace(strFormula, "row_num", activecell.row)</pre>


    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    thanks, for taking a look,

    I have tried using your advice but not sure how to make it actually work in the form.

    I have learned that you can use ref edit to select a cell, so i have added this to my form.

    I have a question about linking the input boxes to offset cells.
    If cell I27 is Selected,
    If a temperature of 100 is added to Lo_Temp textbox
    then 100 will be entered in the worksheet at C27 so a offset of 6 cells to the left
    and 300 is added to Hi_Temp textbox
    300 will be entered in D27 offset of 5 cells

    would i use this logic behind the Fill in Button or the Textboxes?

    Thanks

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    Does the attached give you a start? You will need some error handling for cases where for example the selected cell is not in the right range and you may want to check if there are already values filled in for the selected row.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    Thanks it did get me going
    I did make some modifications. and now stuck in a situation of not knowing the right function to use
    here is my addtions building off your code:

    'Public Const mstrTEMP_FORMULA As String = "=IF(AND($B$3>temp_lo,$B$3<temp_hi),INDEX($A$8:$A$ 17,MATCH($Brow_num,$B$8:$B$17,0)),"""")"
    Private Sub FillinButton_Click()
    Dim strFormula As String, lngRowNum As Long
    'Check to see refedit box has not been cleared by mistake.
    If Me.RefEdit1.Value = "" Then Me.RefEdit1.Value = Selection.Address

    'Build a base formula to use
    strFormula = "=if(and("
    If Me.TemperatureBox.Value = True Then
    strFormula = strFormula & "$B$3>Crow_num,$B$3<Drow_num),"
    End If

    'take care of the comma
    strFormula = strFormula & "true" 'to avoid removing the comma
    strFormula = strFormula & "),INDEX($A$8:$A$17,MATCH($Brow_num,$B$8:$B$17,0)) ,"""")"

    'replace row_num with Me.RefEdit1.Value
    strFormula = Replace(strFormula, "row_num", Me.RefEdit1.Value)
    'Just get row number since any column might have been selected.
    lngRowNum = Sheets("ChemTable").Range(Me.RefEdit1.Value).Row
    With Sheets("ChemTable")
    'Fill in relevant cells (column numbers are fixed)
    .Cells(lngRowNum, 3) = Me.Temp_Lo
    .Cells(lngRowNum, 4) = Me.Temp_Hi
    .Cells(lngRowNum, 5) = Me.FormationText
    .Cells(lngRowNum, 6) = Me.BaseFluidText
    .Cells(lngRowNum, 7) = Me.TreatmentText
    .Cells(lngRowNum, 8) = Me.CompanyText

    .Cells(lngRowNum, 9).Formula = strFormula
    End With
    Unload Me
    End Sub

    The problem I am having is once i have selected a cell
    ex I23, I need just the row num : 23 so i can add it to the column where I want my data to go.

    does any one know how I can do this?

    Thanks very much

  6. #6
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    After using the debug I have found that lngRowNum finds the rownum

    how do you concatenate to a string with a varible?

    strFormula = strFormula & "$B$3>C"&lngRowNum&", $B$3<D"&lngRowNum&"),"

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    um, exactly like that! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    sorry about dup posts,

    strFormula = strFormula & "$B$3>C"&lngRowNum&", $B$3<D"&lngRowNum&"),"

    this methid is not working in my VB code do I need a + or something

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    Try pasting his in to the Userforms code

    'Public Const mstrTEMP_FORMULA As String = "=IF(AND($B$3>temp_lo,$B$3<temp_hi),INDEX($A$8:$A$ 17,MATCH($Brow_num,$B$8:$B$17,0)),"""")"
    Private Sub FillinButton_Click()
    Dim strFormula As String, lngRowNum As Long
    lngRowNum = Sheets("ChemTable").Range(Me.RefEdit1.Value).Row
    'Check to see refedit box has not been cleared by mistake.
    If Me.RefEdit1.Value = "" Then Me.RefEdit1.Value = Selection.Address

    'Build a base formula to use
    strFormula = "=if(and("
    If Me.TemperatureBox.Value = True Then
    strFormula = strFormula & "$B$3>C" & lngRowNum & ", $B$3<D" & lngRowNum & ","
    End If

    'take care of the comma
    strFormula = strFormula & "true" 'to avoid removing the comma
    strFormula = strFormula & "),INDEX($A$8:$A$17,MATCH($Brow_num,$B$8:$B$17,0)) ,"""")"

    'replace row_num with Me.RefEdit1.Value
    strFormula = Replace(strFormula, "row_num", lngRowNum)
    'Just get row number since any column might have been selected.

    With Sheets("ChemTable")
    'Fill in relevant cells (column numbers are fixed)
    .Cells(lngRowNum, 3) = Me.Temp_Lo
    .Cells(lngRowNum, 4) = Me.Temp_Hi
    .Cells(lngRowNum, 5) = Me.FormationText
    .Cells(lngRowNum, 6) = Me.BaseFluidText
    .Cells(lngRowNum, 7) = Me.TreatmentText
    .Cells(lngRowNum, 8) = Me.CompanyText

    .Cells(lngRowNum, 9).Formula = strFormula
    End With
    Unload Me
    End Sub

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    Does this do what you want?

    <code>
    strFormula = strFormula & "$B$3>C" & lngRowNum & ", $B$3<D" & lngRowNum & "),"
    </code>

    You must have spaces on both sides of the ampersand when using it as a concatenation operator.

    If this does not do what you want, would you please tell us what is not working.
    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    Hi Legare,

    Thanks for your reply. I figured out the problem I was having with the concatenation issue.

    I have basically got this form finished but one problem is I have added a choice in the form called no conditions.
    when click it basically means that the user just wants a formula to be used and no conditions in it for a standard no condtions formula.
    The problem is if a conditions formual is used and all the conditions are met and also in the same group a no conditions formula was added both look up which fluid to use there for having duplicates. Refer to my Workbook attached
    J23 and 25 belong to the same group.
    A formula was added to both J23 and J25. J25 has the no conditions formula.
    because both are meeting all the condition required the both show fluid1 on there row.
    I was wonder if any one had a innovative way I could prevent this without making a new column for yet another condtion
    which the nocondtions formula would lookfor to meet a condition.

    Another issue and I just hacked my way around both of them.

    1.
    .Cells(lngRowNum, 3) = (0 & Me.Temp_Lo.Value) + 0 '0 is a way to convert to a number
    .Cells(lngRowNum, 4).Value = (0 & Me.Temp_Hi.Value) + 0
    the vbA does not reconize the values being input as numbers so I had to add a 0 to make them a number is there another way to do this?

    2.
    If Me.CompanyBox.Value = True Then
    strFormula = strFormula & "$B$2=H" & lngRowNum & ","
    End If






    'take care of the comma
    strFormula = strFormula & "true" 'to avoid removing the comma
    i had to add this after the strformula varible because as i build the formula through concatenation. the end always has a
    comma so I just concatenate "true" to the end of the string so I dont have to do a find and remove the last comma so the formula works in the cell its added. Would this be the easiest way around the problem?

    thanks

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Logic to Button in a Form (Excel 2000)

    First, a little advice on using the Lounge. You should not add a new question like this to the end of a thread, you should start a new thread. Some people who could not answer the first question may not read the second question and you might not get an answer. Also, you should not ask multiple unrelated questions in the same post. You might not get all of them answered that way.

    Try this formula in J25 and see if it does what you want:

    <code>
    =IF(J23<>"","",IF(ISERROR(INDEX($A$8:$A$17,MATCH(B 25,$B$8:$B$17,0))),"",INDEX($A$8:$A$17,MATCH( B25,$B$8:$B$17,0))))
    </code>


    The following should convert to a double precision number:

    <code>
    .Cells(lngRowNum, 3) = CDBL(Me.Temp_Lo.Value)
    .Cells(lngRowNum, 4).Value = CDBL(Me.Temp_Hi.Value)
    </code>


    The following should remove the comma:

    <code>
    If Right(strFormula, 1) = "," Then
    strFormula = Left(strFormula, Len(strFormula) - 1)
    End If
    </code>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •