Need new approach on forms I set up

B

Bruce Roberson

I am going through and adapting one of my first projects I
wrote for myself last summer when I was first learning to
work with Excel VB. It had all these print forms on it
that I now need to adapt to another user.

On one of these print forms, I had hard coded a caption
name of "Oneok" in this case to match as an identifier on
the invoice I was printing for that option button.

I could change the caption to read "Virginia Power" but if
the name of the purchaser ever changes, which it will,
then the user would have to go into the properties of the
form and change the caption. I know that is no good, so I
need to know how to maybe link that caption name in that
form to a range name perhaps, and it needs to update
itself whenever the form in question is called.

Can someone help get me started with this approach, or
maybe ask me enough questions to where they can suggest
another approach for what I'm trying to adapt to another
user's work in this case.

Thanks,


Bruce
 
T

Tom Ogilvy

Private Sub Userform_Initialize()
Optionbutton1.Caption = Range("Company_Name").Value
End Sub
 
B

Bob Phillips

Might want to use the Activate event if the form gets hidden rather than
unloaded.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
C

chris

Put this in your form's initialize event
On Error Resume Nex
If Sheets("MySheet").Range("a1").Value <> "" the
Me.commandbutton1.Caption = Sheets("MySheet").Range("a1").Valu
End i

----- Bruce Roberson wrote: ----

I am going through and adapting one of my first projects I
wrote for myself last summer when I was first learning to
work with Excel VB. It had all these print forms on it
that I now need to adapt to another user

On one of these print forms, I had hard coded a caption
name of "Oneok" in this case to match as an identifier on
the invoice I was printing for that option button.

I could change the caption to read "Virginia Power" but if
the name of the purchaser ever changes, which it will,
then the user would have to go into the properties of the
form and change the caption. I know that is no good, so I
need to know how to maybe link that caption name in that
form to a range name perhaps, and it needs to update
itself whenever the form in question is called

Can someone help get me started with this approach, or
maybe ask me enough questions to where they can suggest
another approach for what I'm trying to adapt to another
user's work in this case

Thanks


Bruc
 
T

Tom Ogilvy

.. . . And the company name might get changed in the interim. Otherwise, the
initialize event should work fine.
 
B

Bruce Roberson

Thanks everyone for your quick solutions... I will hopefully get to work on
this again today, and will post back here if I am not able to make it work.
You know me... sometimes I'm a little slow "getting it", but once I do, I'm
fine.

Thanks again....


Bruce
 
B

Bruce Roberson

Chris' suggested setup:
of 'name of the purchaser'

On Error Resume Next
If Sheets("MySheet").Range("a1").Value <> "" then
Me.commandbutton1.Caption = Sheets("MySheet").Range ("a1").Value
End if
Click to expand...

Bruce's attempt to implement above structure:

Private Sub UserForm_Initialize()
On Error Resume Next
If Sheets("Sheet1").Range("Purchaser1").Value <> "" Then
Me.OptInput1.Caption = Sheets("Sheet1").Range
("Purchaser1").Value
End If

End Sub

Ok, this is how I put it in and nothing happened as to the
caption, so somehow I either did not put it in the correct
place, or there is some other detail I have missed.

To get to where I put the code in, I clicked Tools, Macro,
Visual Basic Explorer, then View Project Explorer, and
double clicking the form in question (FrmInput). This
brought up a code window with

Private Sub UserForm(Initialize)

That is where I put the code listed above, but nothing
happened, no messages, no caption change, nothing, nada...

Where did I miss the boat?

Thanks,


Bruce
 
B

Bruce Roberson

I went back and put in Tom's code and it works when
Chris's code did not work. Did Chris' response try to put
more complexity in it than needed to be there. I thought
it looked close to what Tom had with a few extra
precautions perhaps. But once again, its hard to argue
with what works. I'll expand on Tom's code and see if I
run into any problems and then post back here later.
 
Top