Solved: Userform writing to worksheet

[elsteshepard]

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]

With Workbooks("thebook.xls").Worksheets("Data").Range("A1")
.Offset(RowCount, 0).Value = "Change"

[elsteshepard]

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]

Yes it does. That way, network is irrelevant.

[elsteshepard]

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]

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]

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]

hmm, I moved that rowcount piece of code to before open the new workbook

seems to have done the trick!

[elsteshepard]

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]

On VBA, on the Workbook level,


Private Sub Workbook_Open()
frmNameOfTheUserForm.Show
End Sub

[elsteshepard]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Just declare it then



Dim NextFreeRow As Long

[elsteshepard]

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