I have a userform that contains over 24 data elements (textboxes &
comboboxes) used to accept data and place in a spreadsheet. I want to use
the same userform to pull the data, that was originally entered, back into
the userform when a user clicks the commandbutton to EDIT data.

I need to know how to capture the value of the commandbutton as being TRUE
and passing it to the routine that initializes the userform so that I can
GOTO section that pulls this data in.

Thanks,
Les

You have lost me on exactly what you want the button to do. Is it intended as
a toggle button that puts the form into edit mode or exactly what???
--
HTH...

Jim Thomlinson


"WLMPilot" wrote:

> I have a userform that contains over 24 data elements (textboxes &
> comboboxes) used to accept data and place in a spreadsheet. I want to use
> the same userform to pull the data, that was originally entered, back into
> the userform when a user clicks the commandbutton to EDIT data.
>
> I need to know how to capture the value of the commandbutton as being TRUE
> and passing it to the routine that initializes the userform so that I can
> GOTO section that pulls this data in.
>
> Thanks,
> Les

Currently I have two commandbuttons: 1 = Enter Flight Data 2 = Edit
Flight Data

Private Sub CommandButton1_Click() 'ENTER FLIGHT PLAN
FltPlan.Show
End Sub

You can see what button 1 does. Except for the number 1, the procecdure for
CommandButton2 would be a mirror image of above procedure. I want to pass
the value of the commandbutton to the initialization procedure of the
userform. Therefore if "EDIT" is clicked the I can navigate around parts of
the initialization routine to a section that will initialize the userform a
different way than that for ENTER.

Tip: If I am going to edit the data, will will pull data in from the
spreadsheet in the initialization section for EDIT.

Les

"Jim Thomlinson" wrote:

> You have lost me on exactly what you want the button to do. Is it intended as
> a toggle button that puts the form into edit mode or exactly what???
> --
> HTH...
>
> Jim Thomlinson
>
>
> "WLMPilot" wrote:
>
> > I have a userform that contains over 24 data elements (textboxes &
> > comboboxes) used to accept data and place in a spreadsheet. I want to use
> > the same userform to pull the data, that was originally entered, back into
> > the userform when a user clicks the commandbutton to EDIT data.
> >
> > I need to know how to capture the value of the commandbutton as being TRUE
> > and passing it to the routine that initializes the userform so that I can
> > GOTO section that pulls this data in.
> >
> > Thanks,
> > Les

I would add a private module level variable to the declarations section of
the userform:

Private miButton As Long

and a public property to the userform:

Public Property Button(iButton As Long)
miButton = iButton
End Property

I would write a conditional initialization routine in the userform:

Public Sub StartMeUp()
If miButton = 1 Then
' blah blah
ElseIf miButton = 2 Then
' yada yada
Else
' something's not right
End If
End Sub

Then my two procedures loading the userform would look like this:

Private Sub CommandButton1_Click() 'ENTER FLIGHT PLAN
With FltPlan
.Button = 1
.StartMeUp
.Show
End With
End Sub

Private Sub CommandButton2_Click() 'ENTER FLIGHT PLAN
With FltPlan
.Button = 2
.StartMeUp
.Show
End With
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"WLMPilot" <(E-Mail Removed)> wrote in message
news1A3AC31-F3C4-4A77-B82E-(E-Mail Removed)...
> Currently I have two commandbuttons: 1 = Enter Flight Data 2 = Edit
> Flight Data
>
> Private Sub CommandButton1_Click() 'ENTER FLIGHT PLAN
> FltPlan.Show
> End Sub
>
> You can see what button 1 does. Except for the number 1, the procecdure
> for
> CommandButton2 would be a mirror image of above procedure. I want to pass
> the value of the commandbutton to the initialization procedure of the
> userform. Therefore if "EDIT" is clicked the I can navigate around parts
> of
> the initialization routine to a section that will initialize the userform
> a
> different way than that for ENTER.
>
> Tip: If I am going to edit the data, will will pull data in from the
> spreadsheet in the initialization section for EDIT.
>
> Les
>
> "Jim Thomlinson" wrote:
>
>> You have lost me on exactly what you want the button to do. Is it
>> intended as
>> a toggle button that puts the form into edit mode or exactly what???
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "WLMPilot" wrote:
>>
>> > I have a userform that contains over 24 data elements (textboxes &
>> > comboboxes) used to accept data and place in a spreadsheet. I want to
>> > use
>> > the same userform to pull the data, that was originally entered, back
>> > into
>> > the userform when a user clicks the commandbutton to EDIT data.
>> >
>> > I need to know how to capture the value of the commandbutton as being
>> > TRUE
>> > and passing it to the routine that initializes the userform so that I
>> > can
>> > GOTO section that pulls this data in.
>> >
>> > Thanks,
>> > Les

I understand everyting except the first part --> "Private miButton As Long".
I don't understand how the value of Button (within the Commandbutton section)
gets passed to iButton. I am fairly new to VBA, though I have programming
experience in BASIC, COBOL. Until I understand VBA better, if I don't see x
= y format, then I get lost.

I also don't understand why the first line of the initialization section is
changed from what I know to me "normal", ie Private Sub UserForm_Initialize().

I believe I understand the Public Property routine will make miButton value
available to every sub routine in section.

Thanks for your help and any explanation to erase my confusion,
Les


"Jon Peltier" wrote:

> I would add a private module level variable to the declarations section of
> the userform:
>
> Private miButton As Long
>
> and a public property to the userform:
>
> Public Property Button(iButton As Long)
> miButton = iButton
> End Property
>
> I would write a conditional initialization routine in the userform:
>
> Public Sub StartMeUp()
> If miButton = 1 Then
> ' blah blah
> ElseIf miButton = 2 Then
> ' yada yada
> Else
> ' something's not right
> End If
> End Sub
>
> Then my two procedures loading the userform would look like this:
>
> Private Sub CommandButton1_Click() 'ENTER FLIGHT PLAN
> With FltPlan
> .Button = 1
> .StartMeUp
> .Show
> End With
> End Sub
>
> Private Sub CommandButton2_Click() 'ENTER FLIGHT PLAN
> With FltPlan
> .Button = 2
> .StartMeUp
> .Show
> End With
> End Sub
>
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "WLMPilot" <(E-Mail Removed)> wrote in message
> news1A3AC31-F3C4-4A77-B82E-(E-Mail Removed)...
> > Currently I have two commandbuttons: 1 = Enter Flight Data 2 = Edit
> > Flight Data
> >
> > Private Sub CommandButton1_Click() 'ENTER FLIGHT PLAN
> > FltPlan.Show
> > End Sub
> >
> > You can see what button 1 does. Except for the number 1, the procecdure
> > for
> > CommandButton2 would be a mirror image of above procedure. I want to pass
> > the value of the commandbutton to the initialization procedure of the
> > userform. Therefore if "EDIT" is clicked the I can navigate around parts
> > of
> > the initialization routine to a section that will initialize the userform
> > a
> > different way than that for ENTER.
> >
> > Tip: If I am going to edit the data, will will pull data in from the
> > spreadsheet in the initialization section for EDIT.
> >
> > Les
> >
> > "Jim Thomlinson" wrote:
> >
> >> You have lost me on exactly what you want the button to do. Is it
> >> intended as
> >> a toggle button that puts the form into edit mode or exactly what???
> >> --
> >> HTH...
> >>
> >> Jim Thomlinson
> >>
> >>
> >> "WLMPilot" wrote:
> >>
> >> > I have a userform that contains over 24 data elements (textboxes &
> >> > comboboxes) used to accept data and place in a spreadsheet. I want to
> >> > use
> >> > the same userform to pull the data, that was originally entered, back
> >> > into
> >> > the userform when a user clicks the commandbutton to EDIT data.
> >> >
> >> > I need to know how to capture the value of the commandbutton as being
> >> > TRUE
> >> > and passing it to the routine that initializes the userform so that I
> >> > can
> >> > GOTO section that pulls this data in.
> >> >
> >> > Thanks,
> >> > Les
>
>
>

When you run the CommandButton1_Click procedure, the code passes a value of
1 to the form's .Button property (.Button = 1). The Button property reads
iButton as 1 and saves it as the form's variable miButton. Then in the
form's method (sub) StartMeUp, the code looks for the value of miButton so
it knows which branch to execute.

I did not use the UserForm_Initialize subroutine, because it runs as soon as
I execute "With FltPlan". However, what I need is to run ".Button = 1"
before initializing the form. Therefore, I use StartMeUp as a delayed
initialization, if you will.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"WLMPilot" <(E-Mail Removed)> wrote in message
news:BFE41B96-068F-470A-ABB0-(E-Mail Removed)...
>I understand everyting except the first part --> "Private miButton As
>Long".
> I don't understand how the value of Button (within the Commandbutton
> section)
> gets passed to iButton. I am fairly new to VBA, though I have programming
> experience in BASIC, COBOL. Until I understand VBA better, if I don't see
> x
> = y format, then I get lost.
>
> I also don't understand why the first line of the initialization section
> is
> changed from what I know to me "normal", ie Private Sub
> UserForm_Initialize().
>
> I believe I understand the Public Property routine will make miButton
> value
> available to every sub routine in section.
>
> Thanks for your help and any explanation to erase my confusion,
> Les
>
>
> "Jon Peltier" wrote:
>
>> I would add a private module level variable to the declarations section
>> of
>> the userform:
>>
>> Private miButton As Long
>>
>> and a public property to the userform:
>>
>> Public Property Button(iButton As Long)
>> miButton = iButton
>> End Property
>>
>> I would write a conditional initialization routine in the userform:
>>
>> Public Sub StartMeUp()
>> If miButton = 1 Then
>> ' blah blah
>> ElseIf miButton = 2 Then
>> ' yada yada
>> Else
>> ' something's not right
>> End If
>> End Sub
>>
>> Then my two procedures loading the userform would look like this:
>>
>> Private Sub CommandButton1_Click() 'ENTER FLIGHT PLAN
>> With FltPlan
>> .Button = 1
>> .StartMeUp
>> .Show
>> End With
>> End Sub
>>
>> Private Sub CommandButton2_Click() 'ENTER FLIGHT PLAN
>> With FltPlan
>> .Button = 2
>> .StartMeUp
>> .Show
>> End With
>> End Sub
>>
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "WLMPilot" <(E-Mail Removed)> wrote in message
>> news1A3AC31-F3C4-4A77-B82E-(E-Mail Removed)...
>> > Currently I have two commandbuttons: 1 = Enter Flight Data 2 = Edit
>> > Flight Data
>> >
>> > Private Sub CommandButton1_Click() 'ENTER FLIGHT PLAN
>> > FltPlan.Show
>> > End Sub
>> >
>> > You can see what button 1 does. Except for the number 1, the
>> > procecdure
>> > for
>> > CommandButton2 would be a mirror image of above procedure. I want to
>> > pass
>> > the value of the commandbutton to the initialization procedure of the
>> > userform. Therefore if "EDIT" is clicked the I can navigate around
>> > parts
>> > of
>> > the initialization routine to a section that will initialize the
>> > userform
>> > a
>> > different way than that for ENTER.
>> >
>> > Tip: If I am going to edit the data, will will pull data in from the
>> > spreadsheet in the initialization section for EDIT.
>> >
>> > Les
>> >
>> > "Jim Thomlinson" wrote:
>> >
>> >> You have lost me on exactly what you want the button to do. Is it
>> >> intended as
>> >> a toggle button that puts the form into edit mode or exactly what???
>> >> --
>> >> HTH...
>> >>
>> >> Jim Thomlinson
>> >>
>> >>
>> >> "WLMPilot" wrote:
>> >>
>> >> > I have a userform that contains over 24 data elements (textboxes &
>> >> > comboboxes) used to accept data and place in a spreadsheet. I want
>> >> > to
>> >> > use
>> >> > the same userform to pull the data, that was originally entered,
>> >> > back
>> >> > into
>> >> > the userform when a user clicks the commandbutton to EDIT data.
>> >> >
>> >> > I need to know how to capture the value of the commandbutton as
>> >> > being
>> >> > TRUE
>> >> > and passing it to the routine that initializes the userform so that
>> >> > I
>> >> > can
>> >> > GOTO section that pulls this data in.
>> >> >
>> >> > Thanks,
>> >> > Les
>>
>>
>>