Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
Private Sub Userform_Initialize()
Optionbutton1.Caption = Range("Company_Name").Value End Sub -- Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
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) "Tom Ogilvy" wrote in message ... Private Sub Userform_Initialize() Optionbutton1.Caption = Range("Company_Name").Value End Sub -- Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
Put this in your form's initialize event
Whe 'Mysheet' is your sheets name 'commandbutton1' is your buttons nam CHANGE 'Range("a1")' to match the cell address of 'name of the purchaser' 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
.. . . And the company name might get changed in the interim. Otherwise, the
initialize event should work fine. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... 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) "Tom Ogilvy" wrote in message ... Private Sub Userform_Initialize() Optionbutton1.Caption = Range("Company_Name").Value End Sub -- Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
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 "Tom Ogilvy" wrote in message ... . . . And the company name might get changed in the interim. Otherwise, the initialize event should work fine. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... 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) "Tom Ogilvy" wrote in message ... Private Sub Userform_Initialize() Optionbutton1.Caption = Range("Company_Name").Value End Sub -- Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
Chris' suggested setup:
Whe 'Mysheet' is your sheets name 'commandbutton1' is your buttons name CHANGE 'Range("a1")' to match the cell address 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 Bruce's attempt to implement above structu 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need new approach on forms I set up
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. -----Original Message----- Private Sub Userform_Initialize() Optionbutton1.Caption = Range("Company_Name").Value End Sub -- Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What approach should I adopt ..... | Excel Discussion (Misc queries) | |||
Bug or wrong approach | Excel Worksheet Functions | |||
Approach to Excel | Excel Discussion (Misc queries) | |||
What is the right approach? | Excel Worksheet Functions | |||
How to approach this? | Excel Discussion (Misc queries) |