i have this code assigned to a button on each sheet

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

then i want to set 2 values on the form when the button is clicked. i fill
the combo box with the active sheet name (it is a drop down of the 12 month
names, one for each sheet) and i put the line number it is going to enter
the record on right below that in a text box.

Private Sub UserForm_Activate()
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, "B") _
.End(xlUp).Offset(1, 0).Row
UserForm1.TextBox1.Value = "Line Item # " & iRow - 3
UserForm1.ComboBox1.Value = ActiveSheet.Name
Me.LSite.SetFocus
End Sub

it mostly works, but i want the focus to be in the first field, LSite, but
the form is not even active after i click the macro button. after i click
the form to make it the active window, the cursor is still not in the LSite
field.


how do i get around this behavior?
--


Gary

What is LSite? Is it a control on the userform? If so, that code works fine
for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have this code assigned to a button on each sheet

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

then i want to set 2 values on the form when the button is clicked. i
fill
the combo box with the active sheet name (it is a drop down of the 12
month
names, one for each sheet) and i put the line number it is going to enter
the record on right below that in a text box.

Private Sub UserForm_Activate()
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, "B") _
.End(xlUp).Offset(1, 0).Row
UserForm1.TextBox1.Value = "Line Item # " & iRow - 3
UserForm1.ComboBox1.Value = ActiveSheet.Name
Me.LSite.SetFocus
End Sub

it mostly works, but i want the focus to be in the first field, LSite,
but
the form is not even active after i click the macro button. after i click
the form to make it the active window, the cursor is still not in the
LSite
field.


how do i get around this behavior?
--


Gary

LSite is a textbox for the 1st entry.

instead of userform1.show, how would i use a macro to call that code module
within the userform called UserForm_Activate. tried run and call, but i
guess i don't have the syntax correct.

--


Gary


"Bob Phillips" wrote in message
...
What is LSite? Is it a control on the userform? If so, that code works
fine
for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have this code assigned to a button on each sheet

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

then i want to set 2 values on the form when the button is clicked. i
fill
the combo box with the active sheet name (it is a drop down of the 12
month
names, one for each sheet) and i put the line number it is going to enter
the record on right below that in a text box.

Private Sub UserForm_Activate()
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, "B") _
.End(xlUp).Offset(1, 0).Row
UserForm1.TextBox1.Value = "Line Item # " & iRow - 3
UserForm1.ComboBox1.Value = ActiveSheet.Name
Me.LSite.SetFocus
End Sub

it mostly works, but i want the focus to be in the first field, LSite,
but
the form is not even active after i click the macro button. after i click
the form to make it the active window, the cursor is still not in the
LSite
field.


how do i get around this behavior?
--


Gary

Gary,

The problem with accessing a form is that it is not part of the userforms
collection until loaded, but you can force this. As an example, make the
Activate event public, and try this

Dim oUserForm As Object
On Error Resume Next
Set oUserForm = UserForms.Add("Userform1")
oUserForm.Load
oUserForm.UserForm_Activate

although I fail to understand why this would be prefereable to
Userform1.Show.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
What is LSite? Is it a control on the userform? If so, that code works
fine
for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have this code assigned to a button on each sheet

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

then i want to set 2 values on the form when the button is clicked. i
fill
the combo box with the active sheet name (it is a drop down of the 12
month
names, one for each sheet) and i put the line number it is going to
enter
the record on right below that in a text box.

Private Sub UserForm_Activate()
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, "B") _
.End(xlUp).Offset(1, 0).Row
UserForm1.TextBox1.Value = "Line Item # " & iRow - 3
UserForm1.ComboBox1.Value = ActiveSheet.Name
Me.LSite.SetFocus
End Sub

it mostly works, but i want the focus to be in the first field, LSite,
but
the form is not even active after i click the macro button. after i
click
the form to make it the active window, the cursor is still not in the
LSite
field.


how do i get around this behavior?
--


Gary

hi bob, thanks again for your time.

this is my first effort in coding a userform, so please take that into
consideration here.

i have 2 fields i set on opening, one is a combobox dropdown i populate from
a rowsource with the 12 months of the year, so the user can choose which
sheet they want. when the form first loads, (the have to click the button to
load it, i don't want it to load when the sheet is activated unless the form
has already been displayed) depending on which is the active sheet, that
month appears in the dropdown. also, there is a textbox below it, i display
the current line number the that this entry will populate. when i use
userform1.show, the dropdown and textbox are empty.

i then use code posted earlier in the userform activate module to fill these
fields. so the macro button runs userform1.show and then the activate code
populates those fields.

if i just execute the userform activate code, it does the same thing with
out the userform show but it fills the fields. is there another way to
populate these fields instead of the activate property?

--


Gary


"Bob Phillips" wrote in message
...
Gary,

The problem with accessing a form is that it is not part of the userforms
collection until loaded, but you can force this. As an example, make the
Activate event public, and try this

Dim oUserForm As Object
On Error Resume Next
Set oUserForm = UserForms.Add("Userform1")
oUserForm.Load
oUserForm.UserForm_Activate

although I fail to understand why this would be prefereable to
Userform1.Show.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
What is LSite? Is it a control on the userform? If so, that code works
fine
for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have this code assigned to a button on each sheet

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

then i want to set 2 values on the form when the button is clicked. i
fill
the combo box with the active sheet name (it is a drop down of the 12
month
names, one for each sheet) and i put the line number it is going to
enter
the record on right below that in a text box.

Private Sub UserForm_Activate()
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, "B") _
.End(xlUp).Offset(1, 0).Row
UserForm1.TextBox1.Value = "Line Item # " & iRow - 3
UserForm1.ComboBox1.Value = ActiveSheet.Name
Me.LSite.SetFocus
End Sub

it mostly works, but i want the focus to be in the first field, LSite,
but
the form is not even active after i click the macro button. after i
click
the form to make it the active window, the cursor is still not in the
LSite
field.


how do i get around this behavior?
--


Gary

PS

i can zip up and email the workbook if you're interested. it's only 250K

--


Gary


"Bob Phillips" wrote in message
...
Gary,

The problem with accessing a form is that it is not part of the userforms
collection until loaded, but you can force this. As an example, make the
Activate event public, and try this

Dim oUserForm As Object
On Error Resume Next
Set oUserForm = UserForms.Add("Userform1")
oUserForm.Load
oUserForm.UserForm_Activate

although I fail to understand why this would be prefereable to
Userform1.Show.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
What is LSite? Is it a control on the userform? If so, that code works
fine
for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have this code assigned to a button on each sheet

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

then i want to set 2 values on the form when the button is clicked. i
fill
the combo box with the active sheet name (it is a drop down of the 12
month
names, one for each sheet) and i put the line number it is going to
enter
the record on right below that in a text box.

Private Sub UserForm_Activate()
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, "B") _
.End(xlUp).Offset(1, 0).Row
UserForm1.TextBox1.Value = "Line Item # " & iRow - 3
UserForm1.ComboBox1.Value = ActiveSheet.Name
Me.LSite.SetFocus
End Sub

it mostly works, but i want the focus to be in the first field, LSite,
but
the form is not even active after i click the macro button. after i
click
the form to make it the active window, the cursor is still not in the
LSite
field.


how do i get around this behavior?
--


Gary

Gary,

I am not sure I understand yet what you are trying to do, but maybe if you
send it to me it will, be clearer.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
PS

i can zip up and email the workbook if you're interested. it's only 250K

--


Gary


"Bob Phillips" wrote in message
...
Gary,

The problem with accessing a form is that it is not part of the
userforms
collection until loaded, but you can force this. As an example, make the
Activate event public, and try this

Dim oUserForm As Object
On Error Resume Next
Set oUserForm = UserForms.Add("Userform1")
oUserForm.Load
oUserForm.UserForm_Activate

although I fail to understand why this would be prefereable to
Userform1.Show.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
What is LSite? Is it a control on the userform? If so, that code works
fine
for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have this code assigned to a button on each sheet

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

then i want to set 2 values on the form when the button is clicked.
i
fill
the combo box with the active sheet name (it is a drop down of the 12
month
names, one for each sheet) and i put the line number it is going to
enter
the record on right below that in a text box.

Private Sub UserForm_Activate()
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, "B") _
.End(xlUp).Offset(1, 0).Row
UserForm1.TextBox1.Value = "Line Item # " & iRow - 3
UserForm1.ComboBox1.Value = ActiveSheet.Name
Me.LSite.SetFocus
End Sub

it mostly works, but i want the focus to be in the first field,
LSite,
but
the form is not even active after i click the macro button. after i
click
the form to make it the active window, the cursor is still not in the
LSite
field.


how do i get around this behavior?
--


Gary