PDA

View Full Version : Solved: Userform writing to worksheet



elsteshepard
04-29-2009, 02:04 AM
Hi, I currently have a userform which when completed a user clicks submit, which then copies the information to the open worksheet

Is it possible to send this information instead to a different workbook?

rather than saying
With Worksheets("Data").Range("A1")
.Offset(RowCount, 0).Value = "Change"

Can i say for example With worksheet{insert filename here}?

Is it that simple?

Thanks

Bob Phillips
04-29-2009, 03:11 AM
With Workbooks("thebook.xls").Worksheets("Data").Range("A1")
.Offset(RowCount, 0).Value = "Change"

elsteshepard
04-29-2009, 03:28 AM
does workbooks("thebook.xls") have to be open?

i'm on a network, does that make any difference? The filename is
\\areaa\folder1\folder2\folder3\thebook.xls (file://\\areaa\folder1\folder2\folder3\thebook.xls)

Thanks

Bob Phillips
04-29-2009, 03:34 AM
Yes it does. That way, network is irrelevant.

elsteshepard
04-29-2009, 03:42 AM
you can guess what i'm going to ask now...

how can i?
open the workbook
make the changes via the userform (I know this step)
save changes
close the saved workbook

all without the user knowing.

Thanks

Benzadeus
04-29-2009, 03:55 AM
Sub OpenChangeSaveClose()
Dim _
wb As Workbook

Set wb = Workbooks.Open("\\areaa\folder1\folder2\folder3\thebook.xls (file://\\areaa\folder1\folder2\folder3\thebook.xls)")

With wb.Worksheets("Data").Range("A1")
.Offset(RowCount, 0).Value = "Change"
wb.Save
wb.Close
End With

Set wb = Nothing
End Sub

elsteshepard
04-29-2009, 04:52 AM
lovely, thanks - I think it's working...
although i'm having probelms with my rowcount

originally the code was;

RowCount = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Data").Range("A1")
.Offset(RowCount, 0).Value = "Change"

In the new code;

Dim _
wb As Workbook
Set wb = Workbooks.Open("filename.xls")
RowCount = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
With wb.Worksheets("Data").Range("A1")
.Offset(RowCount, 1).Value = "Change"

I'm having trouble - it gives me an error

I presume rowcount helps to add the data to the next free row in the workbook?

I'm stumped.

elsteshepard
04-29-2009, 04:54 AM
hmm, I moved that rowcount piece of code to before open the new workbook

seems to have done the trick!

elsteshepard
04-29-2009, 05:04 AM
Final question about this damn userform - the boss better be happy!

how can i make it so all the user sees is the userform?

i.e. I send them an attachment, or they use sharepoint to open

and all the user sees is the userform - they don't even know it's excel

do-able?

thanks again

Benzadeus
04-29-2009, 05:19 AM
On VBA, on the Workbook level,


Private Sub Workbook_Open()
frmNameOfTheUserForm.Show
End Sub

elsteshepard
04-29-2009, 05:35 AM
oops!
I thought i'd fixed the rowcount thing...

now what happens is;
workbook opens
data gets written to A2:A20

do another
workbook opens
data gets written to A2:A20 - i.e. it overwrites previous...

what am i doing wrong

Thanks

elsteshepard
04-29-2009, 05:36 AM
On VBA, on the Workbook level,


Private Sub Workbook_Open()
frmNameOfTheUserForm.Show
End Sub


thanks Benzadeus - this is what i have already - the user can still see the sheet behind though, anyway of hiding this?

thanks

elsteshepard
04-29-2009, 06:10 AM
making a right pigs ear of this thread sorry

I think I know what's happening
RowCount = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count

this I believ assigns a value to rowcount from the data sheet of the next available row

the trouble is - the workbook that opens has information already in column A - i need it to ignore this, and look at column B instead.

does that make sense?

Bob Phillips
04-29-2009, 08:19 AM
thanks Benzadeus - this is what i have already - the user can still see the sheet behind though, anyway of hiding this?

thanks



Application.Hide

Bob Phillips
04-29-2009, 08:20 AM
making a right pigs ear of this thread sorry

I think I know what's happening
RowCount = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count

this I believ assigns a value to rowcount from the data sheet of the next available row

the trouble is - the workbook that opens has information already in column A - i need it to ignore this, and look at column B instead.

does that make sense?



With Worksheets("Data")

NextFreeRow = .Cells(.Rows.Count, "B").End(xlUp).Row +1
End With

mdmackillop
04-29-2009, 01:57 PM
You can also use a function if you need to determine the last cell for various routines. This will take the column letter or number.


Sub Test()
MsgBox NxtCel(6).Row
MsgBox NxtCel("F").Row
End Sub

Function NxtCel(Col) As Range
Set NxtCel = Cells(Rows.Count, Col).End(xlUp).Offset(1)
End Function

elsteshepard
04-30-2009, 01:43 AM
With Worksheets("Data")

NextFreeRow = .Cells(.Rows.Count, "B").End(xlUp).Row +1
End With


sorry to be a complete idiot
The code i've got so far;

Set wb = Workbooks.Open("book.xls")

With wb.Worksheets("Data").Range("A1")
.Offset(rowcount, 1).Value = "Change"
etc.
etc.
End with

rowcount currently is defined as
rowcount = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count

do I change the definition of rowcount to be NextFreeRow?

If so, I did this, and with the current code, it entered the data in every other row

What am I missing - is there another way
simply put, I need to enter details from a userform into the next available row BUT column A is pre-populated.

I need help (in more ways than this!)

Bob Phillips
04-30-2009, 02:26 AM
Set wb = Workbooks.Open("book.xls")

With wb.Worksheets("Data")

NextFreeRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Cells(NextFreeRow, "A").Value = "Change"
etc.
etc.
End with

elsteshepard
04-30-2009, 03:06 AM
I'm getting an error
Variable not defined for nextfreerow

NextFreeRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Cells(NextFreeRow, "A").Value = "Change"

etc. just to be clear is

.cells(nextfreerow, "B").Value = anothertxtbox1.value
.cells(nextfreerow, "C").Value = anothertxtbox2.value

etc.

Bob Phillips
04-30-2009, 03:10 AM
Just declare it then



Dim NextFreeRow As Long

elsteshepard
05-01-2009, 12:05 AM
Many thanks
I was getting all kinds of wierd errors before when i was declaring it.

Seems to all be working now though, many thanks for everyones help

cheers

(I'm sure i'll be back again!)

Ste