Defined names in VBA Excel

[kattz]

I don't have any idea on writing VBA so I need it to be kept really simple if possible. I like to learn and teach myself but I need a really good starting point for this one. I read a previous thread but I didn't understand it at all %-)

I have a worksheet called Noticeboard. This will be printed out on A1 paper so it is very big and I have defined cells with names to make it easier as some of the cells are merged.
For example, I have a defined cell in the worksheet named Project_Leader_Name.

I need to create a userform (this I can do) but it is the linking of information from the userform to the worksheet I don't understand.

The userform text box will be called txtPLName.

I don't want to use the row count,as the information that needs to be filled in is all over the page and not in neat little lines or rows as it contains various information from org charts to policies and notices.

So, for the "write data to worksheet information", what would the code be?

Any help would be very much appreciated.
regards
Kattz

 

[electricpete]

To refer to the named range value, use: Range("ProjectLeaderName").Value

 

[kattz]

Thanks Electricpete,

for the OK button code, do i start it off with just the code you gave me?

the example which points to rows and offsets etc, starts off with
"With worksheets("sheet1").range (A1")

I am lucky that i only have text boxes to deal with and no combo or list boxes.

Also, I need to be able to print out the completed excel spreadsheet when finished (so at the end of the OK button code i will need to open the print operations etc) do you or someone else know how to do that?
thanks
kattz

 

[SkipVought]

hi,

So, for the "write data to worksheet information", what would the code be?

Range("Project_Leader_Name").Value = txtPLName.Text

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kattz]

Thanks Skip,
that worked a treat I know that was probable very simple, but we all have to start somewhere

what code do i use to close the userform once i have finished entering the data and cleared the userform? (as per the example code below)

Private Sub cmdok_Click()
    Range("manageroffsite_name").Value = txtMgrOffName.Text
    Range("ohscommittee_name").Value = txtOHSname.Text
    Dim Ctl As Control
On Error Resume Next
For Each Ctl In Me.Controls
    Ctl.Value = Null
Next Ctl
End Sub

do you know of any threads or websites that are good for learners of VBA, just to know what all the words mean would be a great start.
cheers

 

[SkipVought]

Private Sub cmdok_Click()
    Range("manageroffsite_name").Value = txtMgrOffName.Text
    Range("ohscommittee_name").Value = txtOHSname.Text
    Dim Ctl As Control
On Error Resume Next
For Each Ctl In Me.Controls
    Ctl.Value = Null
Next Ctl

me.hide
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}