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