Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Excel 2003
I am developing a userform in Excel using MS Visual Basic I would like to be able to have a form text box (?) perform a calculation using numbers entered in previous fields, show the result of that calculation immediately for visual confirmation and then enter that result in the proper field in the database. Example... enter the following into form fields: Contract Cost: (enter no.) Hard Costs: (enter no.) Mark Up: (enter no.) Gross Margin %: (formula; based on Markup/Contract Cost) (View this data immediately on the form and then upon submitting form entering calculation in GM% column with this project. Also are there any limitations as to how many fields I can enter onto a custom userform? Thanks for any help! |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
bassmanfranc -
no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... hth! susan bassmanfranc wrote: Excel 2003 I am developing a userform in Excel using MS Visual Basic I would like to be able to have a form text box (?) perform a calculation using numbers entered in previous fields, show the result of that calculation immediately for visual confirmation and then enter that result in the proper field in the database. Example... enter the following into form fields: Contract Cost: (enter no.) Hard Costs: (enter no.) Mark Up: (enter no.) Gross Margin %: (formula; based on Markup/Contract Cost) (View this data immediately on the form and then upon submitting form entering calculation in GM% column with this project. Also are there any limitations as to how many fields I can enter onto a custom userform? Thanks for any help! |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
"Susan" wrote in message
ups.com... bassmanfranc - no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... Private Sub TextBox1_Change() If Me.TextBox2.Value < "" Then Me.TextBox3.Value = Me.TextBox1.Value / Me.TextBox2.Value End If End Sub Private Sub TextBox2_Change() If Me.TextBox1.Value < "" Then Me.TextBox3.Value = Me.TextBox1.Value / Me.TextBox2.Value End If End Sub |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
thanks bob! i knew it could but done, but didn't know how to do
it...... now i do! i thought it would have to be an event change, didn't think of doing it with an if-statement. :) susan Bob Phillips wrote: "Susan" wrote in message ups.com... bassmanfranc - no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... Private Sub TextBox1_Change() If Me.TextBox2.Value < "" Then Me.TextBox3.Value = Me.TextBox1.Value / Me.TextBox2.Value End If End Sub Private Sub TextBox2_Change() If Me.TextBox1.Value < "" Then Me.TextBox3.Value = Me.TextBox1.Value / Me.TextBox2.Value End If End Sub |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Private Sub TextBox1_Change()
If Me.TextBox2.Value < "" Then Me.TextBox3.Value = Round( _ Me.TextBox1.Value / Me.TextBox2.Value, 2) End If End Sub Private Sub TextBox2_Change() If Me.TextBox1.Value < "" Then Me.TextBox3.Value = Round( _ Me.TextBox1.Value / Me.TextBox2.Value, 2) End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... "Susan" wrote in message ups.com... bassmanfranc - no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... Private Sub TextBox1_Change() If Me.TextBox2.Value < "" Then Me.TextBox3.Value = Me.TextBox1.Value / Me.TextBox2.Value End If End Sub Private Sub TextBox2_Change() If Me.TextBox1.Value < "" Then Me.TextBox3.Value = Me.TextBox1.Value / Me.TextBox2.Value End If End Sub |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
That was extremely helpful and yes it would be great to have it auto calc. if
possible. I am now trying to get the SUM to show decimals (to 2 places only), which is getting the best of me right now. Any thoughts "Susan" wrote: bassmanfranc - no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... hth! susan bassmanfranc wrote: Excel 2003 I am developing a userform in Excel using MS Visual Basic I would like to be able to have a form text box (?) perform a calculation using numbers entered in previous fields, show the result of that calculation immediately for visual confirmation and then enter that result in the proper field in the database. Example... enter the following into form fields: Contract Cost: (enter no.) Hard Costs: (enter no.) Mark Up: (enter no.) Gross Margin %: (formula; based on Markup/Contract Cost) (View this data immediately on the form and then upon submitting form entering calculation in GM% column with this project. Also are there any limitations as to how many fields I can enter onto a custom userform? Thanks for any help! |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
bob's 2 private subs (which ARE event-change subs, duh to me.....) make
it auto calculate. & decimals, a recent post said http://groups.google.com/group/micro...2253a31b8945d9 From: Paul Mathews - view profile Date: Sat, Oct 14 2006 3:58 pm Email: Paul Mathews Groups: microsoft.public.excel.programming Amy, you could do something like this: TextBox.Text = VBA.FormatNumber(Value,2) where, in this example, 2 represents the number of decimal places that will be shown in the text box. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx susan bassmanfranc wrote: That was extremely helpful and yes it would be great to have it auto calc. if possible. I am now trying to get the SUM to show decimals (to 2 places only), which is getting the best of me right now. Any thoughts "Susan" wrote: bassmanfranc - no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... hth! susan bassmanfranc wrote: Excel 2003 I am developing a userform in Excel using MS Visual Basic I would like to be able to have a form text box (?) perform a calculation using numbers entered in previous fields, show the result of that calculation immediately for visual confirmation and then enter that result in the proper field in the database. Example... enter the following into form fields: Contract Cost: (enter no.) Hard Costs: (enter no.) Mark Up: (enter no.) Gross Margin %: (formula; based on Markup/Contract Cost) (View this data immediately on the form and then upon submitting form entering calculation in GM% column with this project. Also are there any limitations as to how many fields I can enter onto a custom userform? Thanks for any help! |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I am really close to closing this form but I am getting Run Time Error 13 -
Type Mismatch when running the following code. HELP!!!! Please :) Private Sub cmdAddPrj_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("ProjectData") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.txtPrjNo.Value) = "" Then Me.txtPrjNo.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtPrjNo.Value ws.Cells(iRow, 2).Value = Me.txtPrjNm.Value ws.Cells(iRow, 9).Value = Me.txtPrjTyp.Value ws.Cells(iRow, 10).Value = Me.txtCon.Value ws.Cells(iRow, 11).Value = Me.txtEst.Value ws.Cells(iRow, 12).Value = Me.txtPm.Value ws.Cells(iRow, 21).Value = Me.txtJobcst.Value ws.Cells(iRow, 22).Value = Me.txtHrdCst.Value ws.Cells(iRow, 23).Value = Me.txtMrkup.Value ws.Cells(iRow, 24).Value = Me.txtGm.Value ws.Cells(iRow, 25).Value = Me.txtSfEa.Value ws.Cells(iRow, 26).Value = Me.txtPrjCstSfEa.Value ws.Cells(iRow, 27).Value = Me.txtHcSfEa.Value ws.Cells(iRow, 28).Value = Me.txtMuSfEa.Value 'clear the data Me.txtPrjNo.Value = "" Me.txtPrjNm.Value = "" Me.txtPrjTyp.Value = "" Me.txtCon.Value = "" Me.txtEst.Value = "" Me.txtPm.Value = "" Me.txtJobcst.Value = "" Me.txtHrdCst.Value = "" Me.txtMrkup.Value = "" Me.txtGm.Value = "" Me.txtSfEa.Value = "" Me.txtPrjCstSfEa.Value = "" Me.txtHcSfEa.Value = "" Me.txtMuSfEa.Value = "" End Sub Private Sub cmdClose_Click() Unload Me End Sub Private Sub TextBox1_Change() End Sub Private Sub Label1_Click() End Sub Private Sub txtHrdCst_Change() If Me.txtSfEa.Value < "" Then Me.txtHcSfEa = FormatCurrency(Me.txtHrdCst.Value / Me.txtSfEa.Value, 2) End If End Sub Private Sub txtJobcst_Change() If Me.txtMrkup.Value < "" Then Me.txtGm.Value = FormatPercent(Me.txtMrkup.Value / Me.txtJobcst.Value, 2) End If End Sub Private Sub txtMrkup_Change() If Me.txtJobcst.Value < "" Then Me.txtGm.Value = FormatPercent(Me.txtMrkup.Value / Me.txtJobcst.Value, 2) End If If Me.txtSfEa.Value < "" Then Me.txtMuSfEa = FormatCurrency(Me.txtMrkup.Value / Me.txtSfEa.Value, 2) End If End Sub Private Sub txtSfEa_Change() If Me.txtJobcst.Value < "" Then Me.txtPrjCstSfEa = FormatCurrency(Me.txtJobcst.Value / Me.txtSfEa.Value, 2) End If If Me.txtHrdCst.Value < "" Then Me.txtHcSfEa = FormatCurrency(Me.txtHrdCst.Value / Me.txtSfEa.Value, 2) End If If Me.txtMrkup.Value < "" Then Me.txtMuSfEa = FormatCurrency(Me.txtMrkup.Value / Me.txtSfEa.Value, 2) End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "Please use the Close Form button!" End If End Sub "Susan" wrote: bob's 2 private subs (which ARE event-change subs, duh to me.....) make it auto calculate. & decimals, a recent post said http://groups.google.com/group/micro...2253a31b8945d9 From: Paul Mathews - view profile Date: Sat, Oct 14 2006 3:58 pm Email: Paul Mathews Groups: microsoft.public.excel.programming Amy, you could do something like this: TextBox.Text = VBA.FormatNumber(Value,2) where, in this example, 2 represents the number of decimal places that will be shown in the text box. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx susan bassmanfranc wrote: That was extremely helpful and yes it would be great to have it auto calc. if possible. I am now trying to get the SUM to show decimals (to 2 places only), which is getting the best of me right now. Any thoughts "Susan" wrote: bassmanfranc - no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... hth! susan bassmanfranc wrote: Excel 2003 I am developing a userform in Excel using MS Visual Basic I would like to be able to have a form text box (?) perform a calculation using numbers entered in previous fields, show the result of that calculation immediately for visual confirmation and then enter that result in the proper field in the database. Example... enter the following into form fields: Contract Cost: (enter no.) Hard Costs: (enter no.) Mark Up: (enter no.) Gross Margin %: (formula; based on Markup/Contract Cost) (View this data immediately on the form and then upon submitting form entering calculation in GM% column with this project. Also are there any limitations as to how many fields I can enter onto a custom userform? Thanks for any help! |
#9
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
What line of code causes the error?
-- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com (email address is on the web site) "bassmanfranc" wrote in message ... I am really close to closing this form but I am getting Run Time Error 13 - Type Mismatch when running the following code. HELP!!!! Please :) Private Sub cmdAddPrj_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("ProjectData") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.txtPrjNo.Value) = "" Then Me.txtPrjNo.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtPrjNo.Value ws.Cells(iRow, 2).Value = Me.txtPrjNm.Value ws.Cells(iRow, 9).Value = Me.txtPrjTyp.Value ws.Cells(iRow, 10).Value = Me.txtCon.Value ws.Cells(iRow, 11).Value = Me.txtEst.Value ws.Cells(iRow, 12).Value = Me.txtPm.Value ws.Cells(iRow, 21).Value = Me.txtJobcst.Value ws.Cells(iRow, 22).Value = Me.txtHrdCst.Value ws.Cells(iRow, 23).Value = Me.txtMrkup.Value ws.Cells(iRow, 24).Value = Me.txtGm.Value ws.Cells(iRow, 25).Value = Me.txtSfEa.Value ws.Cells(iRow, 26).Value = Me.txtPrjCstSfEa.Value ws.Cells(iRow, 27).Value = Me.txtHcSfEa.Value ws.Cells(iRow, 28).Value = Me.txtMuSfEa.Value 'clear the data Me.txtPrjNo.Value = "" Me.txtPrjNm.Value = "" Me.txtPrjTyp.Value = "" Me.txtCon.Value = "" Me.txtEst.Value = "" Me.txtPm.Value = "" Me.txtJobcst.Value = "" Me.txtHrdCst.Value = "" Me.txtMrkup.Value = "" Me.txtGm.Value = "" Me.txtSfEa.Value = "" Me.txtPrjCstSfEa.Value = "" Me.txtHcSfEa.Value = "" Me.txtMuSfEa.Value = "" End Sub Private Sub cmdClose_Click() Unload Me End Sub Private Sub TextBox1_Change() End Sub Private Sub Label1_Click() End Sub Private Sub txtHrdCst_Change() If Me.txtSfEa.Value < "" Then Me.txtHcSfEa = FormatCurrency(Me.txtHrdCst.Value / Me.txtSfEa.Value, 2) End If End Sub Private Sub txtJobcst_Change() If Me.txtMrkup.Value < "" Then Me.txtGm.Value = FormatPercent(Me.txtMrkup.Value / Me.txtJobcst.Value, 2) End If End Sub Private Sub txtMrkup_Change() If Me.txtJobcst.Value < "" Then Me.txtGm.Value = FormatPercent(Me.txtMrkup.Value / Me.txtJobcst.Value, 2) End If If Me.txtSfEa.Value < "" Then Me.txtMuSfEa = FormatCurrency(Me.txtMrkup.Value / Me.txtSfEa.Value, 2) End If End Sub Private Sub txtSfEa_Change() If Me.txtJobcst.Value < "" Then Me.txtPrjCstSfEa = FormatCurrency(Me.txtJobcst.Value / Me.txtSfEa.Value, 2) End If If Me.txtHrdCst.Value < "" Then Me.txtHcSfEa = FormatCurrency(Me.txtHrdCst.Value / Me.txtSfEa.Value, 2) End If If Me.txtMrkup.Value < "" Then Me.txtMuSfEa = FormatCurrency(Me.txtMrkup.Value / Me.txtSfEa.Value, 2) End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "Please use the Close Form button!" End If End Sub "Susan" wrote: bob's 2 private subs (which ARE event-change subs, duh to me.....) make it auto calculate. & decimals, a recent post said http://groups.google.com/group/micro...2253a31b8945d9 From: Paul Mathews - view profile Date: Sat, Oct 14 2006 3:58 pm Email: Paul Mathews Groups: microsoft.public.excel.programming Amy, you could do something like this: TextBox.Text = VBA.FormatNumber(Value,2) where, in this example, 2 represents the number of decimal places that will be shown in the text box. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx susan bassmanfranc wrote: That was extremely helpful and yes it would be great to have it auto calc. if possible. I am now trying to get the SUM to show decimals (to 2 places only), which is getting the best of me right now. Any thoughts "Susan" wrote: bassmanfranc - no, there is no limit to the number of textboxes, except for the size of your userform. but you can get around that by using a multiform with several pages (will increase the amount of space for textboxes). as for performing calculations, you can do that.... see example below: Option Explicit Sub userform1_initialize() TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" End Sub Sub commandbutton1_click() Dim TextBox1 As Control Dim TextBox2 As Control Dim TextBox3 As Control Dim SUM As Integer SUM = Me.TextBox1.Value / Me.TextBox2.Value Me.TextBox3.Value = SUM End Sub but it doesn't calculate until you click the control button, which i labeled "calculate". maybe somebody else can have it automatically calculate with an event change tabbing from textbox2.......... hth! susan bassmanfranc wrote: Excel 2003 I am developing a userform in Excel using MS Visual Basic I would like to be able to have a form text box (?) perform a calculation using numbers entered in previous fields, show the result of that calculation immediately for visual confirmation and then enter that result in the proper field in the database. Example... enter the following into form fields: Contract Cost: (enter no.) Hard Costs: (enter no.) Mark Up: (enter no.) Gross Margin %: (formula; based on Markup/Contract Cost) (View this data immediately on the form and then upon submitting form entering calculation in GM% column with this project. Also are there any limitations as to how many fields I can enter onto a custom userform? Thanks for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Macro and VB development | Excel Programming | |||
Development considerations for Win XP | Excel Programming | |||
.NET, CAS and Excel Development | Excel Programming | |||
C# vba development | Excel Programming | |||
A development of a Macro by Tom | Excel Programming |