Hi

I use Excel 2K

I have a form with a number of textboxes. I would like some of these
textboxes to default to a particular number. This is so the user does not
have to keep typing a number that is usually always the same for each entry.

This is the code I presently use in the form:-

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DATA")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TxtDate.Value) = "" Then
Me.TxtDate.SetFocus
MsgBox "Please enter a the date"
Exit Sub
End If
----------------------------------------------------------------------

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtDate.Value
ws.Cells(iRow, 2).Value = Me.TxtWeek.Value
ws.Cells(iRow, 3).Value = Me.TxtShift.Value
ws.Cells(iRow, 4).Value = Me.TxtCrew.Value
ws.Cells(iRow, 5).Value = Me.TxtNonProdDel.Value
ws.Cells(iRow, 6).Value = Me.TxtCalShift.Value
ws.Cells(iRow, 7).Value = Me.TxtInput.Value
ws.Cells(iRow, 8).Value = Me.TxtOutput.Value
ws.Cells(iRow, 9).Value = Me.TxtDelays.Value
ws.Cells(iRow, 10).Value = Me.TxtCoils.Value
ws.Cells(iRow, 11).Value = Me.TxtThrd.Value
ws.Cells(iRow, 12).Value = Me.TxtEps.Value
ws.Cells(iRow, 13).Value = Me.TxtType.Value
ws.Cells(iRow, 14).Value = Me.TxtNpft.Value
ws.Cells(iRow, 15).Value = Me.TxtScrp.Value
ws.Cells(iRow, 16).Value = Me.TxtDwnGrd.Value
ws.Cells(iRow, 17).Value = Me.TxtRawCoil.Value
ws.Cells(iRow, 18).Value = Me.TxtInj.Value
ws.Cells(iRow, 19).Value = Me.TxtSlowRun.Value
ws.Cells(iRow, 20).Value = Me.TxtPlanOutput.Value
ws.Cells(iRow, 21).Value = Me.TxtBudgOutput.Value

---------------------------------------------------------------------------
'As an option to the code below, unload and reload the form...
Unload Me
EntryForm.Show

End Sub
-----------------------------------------------------------------------------

Private Sub cmdClose_Click()
Unload Me


End Sub
-------------------------------------------------------------------------------

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
-----------------------------------------------------------------------

Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim sEntry As String
Dim iLoc As Integer

sEntry = Trim(Me.TxtDate.Value)
iLoc = InStr(sEntry, "/")
If iLoc 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" & Left$(sEntry,
iLoc - 1)
On Error Resume Next
Me.TxtDate.Value = Format(CDate(sEntry), "dd-mmm-yy")
If Err < 0 Then
GoTo Had_Problem
End If
Exit Sub
End If
-------------------------------------------------------------------------------------
Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of d/m."
& vbLf & "Please try again..."
Cancel = True

End Sub


So, for example if I wanted the text box (textWeek) to always default to 20
(unless I physically overwrite it) how would I change the code to accomodate
this?


Thanks

John

Hi John,

There are a couple of ways of doing this. One is to set the TextBox property
Linkedcell to a cell somewhere in the workbook and enter the value in that
cell.

Another way is to use the got focus event to set the value when the user
clicks in the textbox.

Private Sub TextWeek_GotFocus()
Me.TextWeek = 20
End Sub

--
Regards,

OssieMac

Thanks for your quick response.

I pasted your code onto the cose for the form but it does not seem to work?
Maybe I am pasting it into the wrong place?

Can you help?

The other method of putting the value somewhere else in the spreadsheet is
not really what I am looking for.

On the form I have a "enter button" that copies all the entries into a
worksheet and clears the form. I would like that when the Week is entered by
the user it stays in the box after each time I press the enter button until
such times the user decides to enter a new week number.

Home this helps

Thanks

John

"OssieMac" wrote:

Hi John,

There are a couple of ways of doing this. One is to set the TextBox property
Linkedcell to a cell somewhere in the workbook and enter the value in that
cell.

Another way is to use the got focus event to set the value when the user
clicks in the textbox.

Private Sub TextWeek_GotFocus()
Me.TextWeek = 20
End Sub

--
Regards,

OssieMac

Is this a user form that you are referring to by any chance?
in which case you should use a user form open event
with userform1
textbox1.value = 20
end with
etc

"John Calder" wrote:

Thanks for your quick response.

I pasted your code onto the cose for the form but it does not seem to work?
Maybe I am pasting it into the wrong place?

Can you help?

The other method of putting the value somewhere else in the spreadsheet is
not really what I am looking for.

On the form I have a "enter button" that copies all the entries into a
worksheet and clears the form. I would like that when the Week is entered by
the user it stays in the box after each time I press the enter button until
such times the user decides to enter a new week number.

Home this helps

Thanks

John

"OssieMac" wrote:

Hi John,

There are a couple of ways of doing this. One is to set the TextBox property
Linkedcell to a cell somewhere in the workbook and enter the value in that
cell.

Another way is to use the got focus event to set the value when the user
clicks in the textbox.

Private Sub TextWeek_GotFocus()
Me.TextWeek = 20
End Sub

--
Regards,

OssieMac

is there anything wrong with just presetting the value like this?

Private Sub UserForm_Activate()
Textweek.Value = 20
End Sub

"John Calder" wrote:

Hi

I use Excel 2K

I have a form with a number of textboxes. I would like some of these
textboxes to default to a particular number. This is so the user does not
have to keep typing a number that is usually always the same for each entry.

This is the code I presently use in the form:-

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DATA")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TxtDate.Value) = "" Then
Me.TxtDate.SetFocus
MsgBox "Please enter a the date"
Exit Sub
End If
----------------------------------------------------------------------

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtDate.Value
ws.Cells(iRow, 2).Value = Me.TxtWeek.Value
ws.Cells(iRow, 3).Value = Me.TxtShift.Value
ws.Cells(iRow, 4).Value = Me.TxtCrew.Value
ws.Cells(iRow, 5).Value = Me.TxtNonProdDel.Value
ws.Cells(iRow, 6).Value = Me.TxtCalShift.Value
ws.Cells(iRow, 7).Value = Me.TxtInput.Value
ws.Cells(iRow, 8).Value = Me.TxtOutput.Value
ws.Cells(iRow, 9).Value = Me.TxtDelays.Value
ws.Cells(iRow, 10).Value = Me.TxtCoils.Value
ws.Cells(iRow, 11).Value = Me.TxtThrd.Value
ws.Cells(iRow, 12).Value = Me.TxtEps.Value
ws.Cells(iRow, 13).Value = Me.TxtType.Value
ws.Cells(iRow, 14).Value = Me.TxtNpft.Value
ws.Cells(iRow, 15).Value = Me.TxtScrp.Value
ws.Cells(iRow, 16).Value = Me.TxtDwnGrd.Value
ws.Cells(iRow, 17).Value = Me.TxtRawCoil.Value
ws.Cells(iRow, 18).Value = Me.TxtInj.Value
ws.Cells(iRow, 19).Value = Me.TxtSlowRun.Value
ws.Cells(iRow, 20).Value = Me.TxtPlanOutput.Value
ws.Cells(iRow, 21).Value = Me.TxtBudgOutput.Value

---------------------------------------------------------------------------
'As an option to the code below, unload and reload the form...
Unload Me
EntryForm.Show

End Sub
-----------------------------------------------------------------------------

Private Sub cmdClose_Click()
Unload Me


End Sub
-------------------------------------------------------------------------------

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
-----------------------------------------------------------------------

Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim sEntry As String
Dim iLoc As Integer

sEntry = Trim(Me.TxtDate.Value)
iLoc = InStr(sEntry, "/")
If iLoc 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" & Left$(sEntry,
iLoc - 1)
On Error Resume Next
Me.TxtDate.Value = Format(CDate(sEntry), "dd-mmm-yy")
If Err < 0 Then
GoTo Had_Problem
End If
Exit Sub
End If
-------------------------------------------------------------------------------------
Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of d/m."
& vbLf & "Please try again..."
Cancel = True

End Sub


So, for example if I wanted the text box (textWeek) to always default to 20
(unless I physically overwrite it) how would I change the code to accomodate
this?


Thanks

John

Thanks for the response

I tried the code you suggested in both the userform code and the module code
but neither worked. I just get a debug error.

Also, if I was to place a default number (lets say 20) in the code would it
not mean that it would always display the number 20?

What I am looking for is for the textbox to display the last number entered
and default to whatever that number is until the user changes it.

So, if a user enters 20, then each time the form clears for new data to be
enetered the number 20 remains until there is a requirement to change it.
When the number 20 is then changed to the number 21 then 21 will display each
time the form data has been entered.

Thanks


John



"Atishoo" wrote:

is there anything wrong with just presetting the value like this?

Private Sub UserForm_Activate()
Textweek.Value = 20
End Sub

"John Calder" wrote:

Hi

I use Excel 2K

I have a form with a number of textboxes. I would like some of these
textboxes to default to a particular number. This is so the user does not
have to keep typing a number that is usually always the same for each entry.

This is the code I presently use in the form:-

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DATA")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TxtDate.Value) = "" Then
Me.TxtDate.SetFocus
MsgBox "Please enter a the date"
Exit Sub
End If
----------------------------------------------------------------------

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtDate.Value
ws.Cells(iRow, 2).Value = Me.TxtWeek.Value
ws.Cells(iRow, 3).Value = Me.TxtShift.Value
ws.Cells(iRow, 4).Value = Me.TxtCrew.Value
ws.Cells(iRow, 5).Value = Me.TxtNonProdDel.Value
ws.Cells(iRow, 6).Value = Me.TxtCalShift.Value
ws.Cells(iRow, 7).Value = Me.TxtInput.Value
ws.Cells(iRow, 8).Value = Me.TxtOutput.Value
ws.Cells(iRow, 9).Value = Me.TxtDelays.Value
ws.Cells(iRow, 10).Value = Me.TxtCoils.Value
ws.Cells(iRow, 11).Value = Me.TxtThrd.Value
ws.Cells(iRow, 12).Value = Me.TxtEps.Value
ws.Cells(iRow, 13).Value = Me.TxtType.Value
ws.Cells(iRow, 14).Value = Me.TxtNpft.Value
ws.Cells(iRow, 15).Value = Me.TxtScrp.Value
ws.Cells(iRow, 16).Value = Me.TxtDwnGrd.Value
ws.Cells(iRow, 17).Value = Me.TxtRawCoil.Value
ws.Cells(iRow, 18).Value = Me.TxtInj.Value
ws.Cells(iRow, 19).Value = Me.TxtSlowRun.Value
ws.Cells(iRow, 20).Value = Me.TxtPlanOutput.Value
ws.Cells(iRow, 21).Value = Me.TxtBudgOutput.Value

---------------------------------------------------------------------------
'As an option to the code below, unload and reload the form...
Unload Me
EntryForm.Show

End Sub
-----------------------------------------------------------------------------

Private Sub cmdClose_Click()
Unload Me


End Sub
-------------------------------------------------------------------------------

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
-----------------------------------------------------------------------

Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim sEntry As String
Dim iLoc As Integer

sEntry = Trim(Me.TxtDate.Value)
iLoc = InStr(sEntry, "/")
If iLoc 0 Then
sEntry = Right$(sEntry, Len(sEntry) - iLoc) & "/" & Left$(sEntry,
iLoc - 1)
On Error Resume Next
Me.TxtDate.Value = Format(CDate(sEntry), "dd-mmm-yy")
If Err < 0 Then
GoTo Had_Problem
End If
Exit Sub
End If
-------------------------------------------------------------------------------------
Had_Problem:
MsgBox "Could not interpret your entry as a date in the format of d/m."
& vbLf & "Please try again..."
Cancel = True

End Sub


So, for example if I wanted the text box (textWeek) to always default to 20
(unless I physically overwrite it) how would I change the code to accomodate
this?


Thanks

John

My apologies John,

I was in fairyland when I wrote the following. Discard it because the
Controlsource updates automatically.

If you use the Controlsource method then the value in the cell needs to be
updated each time the textbox is updated. Code similar to the following in
the Userform code module.

Private Sub TextWeek_AfterUpdate()
Me.TextWeek = Sheets("Sheet1").Range("A1")
End Sub


--
Regards,

OssieMac


"OssieMac" wrote:

Hello again John,

Firstly the Got focus code is for a text box on a worksheet. The code needs
to be in the on enter event for a userform.

Private Sub TextWeek_Enter()
Me.TextWeek = 20
End Sub

However, your further posts indicate that this is not what you want.
Therefore, while it might not be your preferred option, I think that you
should set the Controlsource property of the textbox to a cell on a
worksheet. Note that you can hide the column or row with the value or even
hide the worksheet.

If you don't want the user to be able to unhide the worksheet then hide it
with code like the following in a module and then it can only be unhidden
with code. (I set the variable ws because VBA intellisense brings up
dropdowns with the code options that can be used.)

Sub HideMyWorkSheet()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
'Hide so cannot unhide in the interactive mode
ws.Visible = xlSheetVeryHidden
End Sub

Sub UnHideMyWorkSheet()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Visible = xlSheetVisible
End Sub


If you use the Controlsource method then the value in the cell needs to be
updated each time the textbox is updated. Code similar to the following in
the Userform code module.

Private Sub TextWeek_AfterUpdate()
Me.TextWeek = Sheets("Sheet1").Range("A1")
End Sub

Note when setting the Controlsource in the textbox properties set it with
both the sheet name and cell like this.

Sheet1!A1

--
Regards,

OssieMac

Hello again John,

Firstly the Got focus code is for a text box on a worksheet. The code needs
to be in the on enter event for a userform.

Private Sub TextWeek_Enter()
Me.TextWeek = 20
End Sub

However, your further posts indicate that this is not what you want.
Therefore, while it might not be your preferred option, I think that you
should set the Controlsource property of the textbox to a cell on a
worksheet. Note that you can hide the column or row with the value or even
hide the worksheet.

If you don't want the user to be able to unhide the worksheet then hide it
with code like the following in a module and then it can only be unhidden
with code. (I set the variable ws because VBA intellisense brings up
dropdowns with the code options that can be used.)

Sub HideMyWorkSheet()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
'Hide so cannot unhide in the interactive mode
ws.Visible = xlSheetVeryHidden
End Sub

Sub UnHideMyWorkSheet()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Visible = xlSheetVisible
End Sub


If you use the Controlsource method then the value in the cell needs to be
updated each time the textbox is updated. Code similar to the following in
the Userform code module.

Private Sub TextWeek_AfterUpdate()
Me.TextWeek = Sheets("Sheet1").Range("A1")
End Sub

Note when setting the Controlsource in the textbox properties set it with
both the sheet name and cell like this.

Sheet1!A1

--
Regards,

OssieMac