Sum and Counta function in Listbox for UserForm

[Shazi]

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

 

[Nigel]

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
(e-mail address removed)

 

[Shazi]

You can link the cell to the UserForm control by setting the  ControlSource
property of the control, either in the object properties within VB Editoror
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
(e-mail address removed)








































- 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