| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum and Counta function in Listbox for UserForm
Hi,
I design the Userform for the Query, its working very nice, in this form I can get the filtered data in the listbox. I need two extra textbox in my userform to display the total number of Records and the Sum of the total amount. how to make this I dont know. I put the formula in the worksheet, and connect to the Userform with textbox, upon loading the userform its deleting the formula in the worksheet. Pls help me out how to make this procedure. Waiting for reply. With Best Regard. Pls fine below my procedure in the UserForm. Syed Shahzad Zafar Madinah Option Explicit Private Sub UserForm_Initialize() HideTitleBar Me ' this command is link to Macro: Hide_TitleBar of userform Sheets("DailyIssue").Select Range("A1").Activate StartDate = "" EndDate = "" MaterialName = "" CoboCategory = "" TxtTRNo = "" CoboEmployee = "" StartDate.SetFocus End Sub Private Sub UserForm_Activate() Call UnprotectAllSheets End Sub Private Sub CommandButton1_Click() ' Ok Button Range("A4:J5000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _ ("AP1:AY3"), CopyToRange:=Range("AP4:AY5000"), Unique:=False Sheets("DailyIssue").Select Range("AP3:AY3") = "" End Sub Private Sub CommandButton4_Click() ' Export Button to prepare report Sheets("DailyIssue").Select Range("AP5:AY500").Select Selection.Copy Sheets("ReportIssue").Select Range("A5").Select ActiveSheet.Paste Range("F1").Select Sheets("DailyIssue").Select Range("AP3:AY3") = "" Range("A4").Select 'clear all searched data to control size of file Sheets("DailyIssue").Select Range("AP5:AY5000") = "" Sheets("ReportIssue").Select ' del all blank rows after data, to control size of file Call DelIssueBlankRowsBottom Call ProtectAllSheets Unload Me End Sub Private Sub CommandButton5_Click() ' Back to Issue Form (Data Entry)Button Unload Me DailyIssueDataEntry.Show End Sub Private Sub CommandButton2_Click() ' Close Form Button Unload Me 'clear all searched data to control size of file Sheets("DailyIssue").Select Range("AP5:AY5000") = "" Call ProtectAllSheets End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ' Disable the "X" on the userform so that the user can't If CloseMode = vbFormControlMenu Then Cancel = True MsgBox Prompt:=" Sorry but I can't let you do that. " End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum and Counta function in Listbox for UserForm
On Jul 10, 8:22*am, "Nigel" wrote:
You can link the cell to the UserForm control by setting the *ControlSource property of the control, either in the object properties within VB Editor or in code within the UserForm (typically the initialize event). * *TextBox1.ControlSource = "Sheet1!A1" Using a text box does means any changes to the textbox value will result in changes to the cell. This is I think your problem? By setting the value property directly with a range reference avoids this and you can control the writing to and from the sheet. Any changes in the cell will not be automatically updated in the UserForm control, so you need to arrange to refresh it. *TextBox1.Value = Sheets("Sheet1").Range("A1") Prevent changes to the control affecting the sheet by disabling the control. TextBox1.Enabled = False Alternatively use a label to display the cell value Label1.Caption = "Total: " & *Sheets("Sheet1").Range("A1") You will need to arrange to refresh this whenever changes are made. -- Regards, Nigel "Shazi" wrote in message ... Hi, I design the Userform for the Query, its working very nice, in this form I can get the filtered data in the listbox. I need two extra textbox in my userform to display the total number of Records and the Sum of the total amount. how to make this I dont know. I put the formula in the worksheet, and connect to the Userform with textbox, upon loading the userform its deleting the formula in the worksheet. Pls help me out how to make this procedure. Waiting for reply. With Best Regard. Pls fine below my procedure in the UserForm. Syed Shahzad Zafar Madinah Option Explicit Private Sub UserForm_Initialize() HideTitleBar Me * * ' this command is link to Macro: Hide_TitleBar of userform * * * *Sheets("DailyIssue").Select * * * *Range("A1").Activate * * * *StartDate = "" * * * *EndDate = "" * * * *MaterialName = "" * * * *CoboCategory = "" * * * *TxtTRNo = "" * * * *CoboEmployee = "" * * * *StartDate.SetFocus End Sub Private Sub UserForm_Activate() * *Call UnprotectAllSheets End Sub Private Sub CommandButton1_Click() ' * Ok Button Range("A4:J5000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _ * * * *("AP1:AY3"), CopyToRange:=Range("AP4:AY5000"), Unique:=False * *Sheets("DailyIssue").Select * *Range("AP3:AY3") = "" End Sub Private Sub CommandButton4_Click() ' * Export Button to prepare report * *Sheets("DailyIssue").Select * *Range("AP5:AY500").Select * *Selection.Copy * *Sheets("ReportIssue").Select * *Range("A5").Select * *ActiveSheet.Paste * *Range("F1").Select * *Sheets("DailyIssue").Select * *Range("AP3:AY3") = "" * *Range("A4").Select * *'clear all searched data to control size of file * *Sheets("DailyIssue").Select * *Range("AP5:AY5000") = "" * *Sheets("ReportIssue").Select * *' del all blank rows after data, to control size of file * *Call DelIssueBlankRowsBottom * *Call ProtectAllSheets * *Unload Me End Sub Private Sub CommandButton5_Click() ' * Back to Issue Form (Data Entry)Button * *Unload Me * *DailyIssueDataEntry.Show End Sub Private Sub CommandButton2_Click() ' * Close Form Button * *Unload Me * *'clear all searched data to control size of file * *Sheets("DailyIssue").Select * *Range("AP5:AY5000") = "" * *Call ProtectAllSheets End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ' Disable the "X" on the userform so that the user can't If CloseMode = vbFormControlMenu Then * *Cancel = True * *MsgBox Prompt:=" Sorry but I can't let you do that. " End If End Sub- Hide quoted text - - Show quoted text - Hi, Dear Mr. Nigel Thank you for spending time for me, the solution you sent to me I applied in my program, and its working very good. this is the solution I was expected., you understand what I want exactly and what I was doing exactly. now my problem is solved. thank you once again.... with best regards. Shahzad Zafar Madinah |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTA Function | Excel Worksheet Functions | |||
| Counta function | Excel Worksheet Functions | |||
| Using the COUNTA function | Excel Discussion (Misc queries) | |||
| userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
| COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Linear Mode
