Results 1 to 12 of 12

20051017, 22:29 #1
 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,

20051018, 06:28 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,300
 Thanks
 3
 Thanked 204 Times in 188 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

20051018, 15:35 #3
 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

20051019, 11:27 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,300
 Thanks
 3
 Thanked 204 Times in 188 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

20051020, 20:26 #5
 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

20051020, 20:46 #6
 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&"),"

20051020, 21:04 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,300
 Thanks
 3
 Thanked 204 Times in 188 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

20051020, 21:07 #8
 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

20051020, 21:25 #9
 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

20051020, 22:19 #10
 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

20051028, 22:14 #11
 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

20051029, 01:52 #12
 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