Output information of a form.

B

Botje

New Member
Joined
Aug 23, 2011
Messages
48
Hey there!

Been searching the web for a while now.. but can't find it.. So this is my last hope!

I made a form for my work. Where people can fill in their Ideas.
There are around 30 people that can fill in their ideas but everytime somebody fills in an idea. The information that was filled in will be shown @ B1 and removes the old idea.
What i want to achieve is everytime somebody fills in this form the information will end up onto the next line.

If B1 is filled. I want it at B2 for example.

Thanks for your input.


PS. Would it be smart to send it to another excel-sheet?



Code: 
Private Sub cmdinvullen_Click()
Dim RowCount As Long
RowCount = Worksheets("Archief").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1")
.Offset(RowCount, 2).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value
If Me.chkTijd.Value = True Then
.Offset(RowCount, 30).Value = "Tijd"
Else
.Offset(RowCount, 30).Value = ""
End If
If Me.chkGeld.Value = True Then
.Offset(RowCount, 31).Value = "Geld"
Else
.Offset(RowCount, 31).Value = ""
End If
If Me.chkCollegas.Value = True Then
.Offset(RowCount, 32).Value = "Collega's"
Else
.Offset(RowCount, 32).Value = ""
End If
If Me.chkToestemming.Value = True Then
.Offset(RowCount, 33).Value = "Toestemming"
Else
.Offset(RowCount, 33).Value = ""
End If
If Me.chkRuimte.Value = True Then
.Offset(RowCount, 34).Value = "Ruimte"
Else
.Offset(RowCount, 34).Value = ""
End If
If Me.chkAnders.Value = True Then
.Offset(RowCount, 35).Value = "Anders"
Else
.Offset(RowCount, 36).Value = ""
End If
If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 40).Value = "Achmea Vitale"
Else
.Offset(RowCount, 40).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 41).Value = "Keuringen"
Else
.Offset(RowCount, 41).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 42).Value = "Klantenservice"
Else
.Offset(RowCount, 42).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 43).Value = "Frontoffice"
Else
.Offset(RowCount, 43).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 44).Value = "Exoten"
Else
.Offset(RowCount, 44).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 45).Value = "MO MKB"
Else
.Offset(RowCount, 45).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 46).Value = "DAM"
Else
.Offset(RowCount, 46).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 47).Value = "Bedrijfsartsen en Consulenten"
Else
.Offset(RowCount, 47).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 48).Value = "RAM"
Else
.Offset(RowCount, 48).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 49).Value = "SAM"
Else
.Offset(RowCount, 49).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 50).Value = "MO AMI"
Else
.Offset(RowCount, 50).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 51).Value = "MO GM AT5"
Else
.Offset(RowCount, 51).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 52).Value = "Interventie Coördinatoren"
Else
.Offset(RowCount, 52).Value = ""
End If
.Offset(RowCount, 3).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
    If Me.TxtNaam.Value = "" Then
    MsgBox "Vul alsjeblieft je naam in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.TxtNaam.SetFocus
    Exit Sub
End If
    If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If
    Unload Me
End Sub
Private Sub cmdsluiten_Click()
    Unload Me
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Welcome to MrExcel.

What's in A1? If it's empty try changing A1 to B1 in:

Code: 
RowCount = Worksheets("Archief").Range("A1").CurrentRegion.Rows.Count
 
Upvote 0
Code: 
With Worksheets("Archief").Range("B1").end(xldown).offset(1)
This should drop you on to the next available row, so you won't need all the .offset(rowcount... etc
 
Upvote 0
Thnx for the quick responses.

A1 reffers to the Top left cell. Nothing specific.

Weaver said:
Code: 
With Worksheets("Archief").Range("B1").end(xldown).offset(1)
This should drop you on to the next available row, so you won't need all the .offset(rowcount... etc
Click to expand...

Below is the code I Currently use.

Code: 
Dim RowCount As Long
RowCount = Worksheets("Archief").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1").End(xlDown).Offset(1)
.Offset(RowCount, 2).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value

It does work except the fact that the information i give stays on the same line. It replaces the old information.

What i wanted to achieve that if i fill in a form the form checks if there is information. If there is it goes to the next line.

Example:

When line 1 is filled, the information I fill in the form automaticly goes to line 2 etc.

Thanks,
 
Last edited:
Upvote 0
Botje said:
Example:

When line 1 is filled, the information I fill in the form automaticly goes to line 2 etc.

Thanks,
Click to expand...

That's what should be happening with your original code because you are offsetting by RowCount which is the number of rows in A1's current region.
 
Upvote 0
Why not use something simple like this to find the next blank row?
Code: 
NextRow = Worksheets("Archief").Range("B"& Rows.Count).End(xlup).Row + 1

You can then use that to put the data on the worksheet.
Code: 
Worksheets("Archief").Range("B"& NextRow).Value =Me.txtidee.Value

Worksheets("Archief").Range("D"& NextRow).Value =Me.txtNaam.Value
 
Upvote 0
I have no clue at all why.. But somehow its resolved... And works properly

Code: 
Private Sub cmdinvullen_Click()
Dim RowCount As Long
RowCount = Worksheets("Archief").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1")
.Offset(RowCount, 2).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value
If Me.chkTijd.Value = True Then
.Offset(RowCount, 30).Value = "Tijd"
Else
.Offset(RowCount, 30).Value = ""
End If
If Me.chkGeld.Value = True Then
.Offset(RowCount, 31).Value = "Geld"
Else
.Offset(RowCount, 31).Value = ""
End If
If Me.chkCollegas.Value = True Then
.Offset(RowCount, 32).Value = "Collega's"
Else
.Offset(RowCount, 32).Value = ""
End If
If Me.chkToestemming.Value = True Then
.Offset(RowCount, 33).Value = "Toestemming"
Else
.Offset(RowCount, 33).Value = ""
End If
If Me.chkRuimte.Value = True Then
.Offset(RowCount, 34).Value = "Ruimte"
Else
.Offset(RowCount, 34).Value = ""
End If
If Me.chkAnders.Value = True Then
.Offset(RowCount, 35).Value = "Anders"
Else
.Offset(RowCount, 36).Value = ""
End If
If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 40).Value = "Achmea Vitale"
Else
.Offset(RowCount, 40).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 41).Value = "Keuringen"
Else
.Offset(RowCount, 41).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 42).Value = "Klantenservice"
Else
.Offset(RowCount, 42).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 43).Value = "Frontoffice"
Else
.Offset(RowCount, 43).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 44).Value = "Exoten"
Else
.Offset(RowCount, 44).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 45).Value = "MO MKB"
Else
.Offset(RowCount, 45).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 46).Value = "DAM"
Else
.Offset(RowCount, 46).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 47).Value = "Bedrijfsartsen en Consulenten"
Else
.Offset(RowCount, 47).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 48).Value = "RAM"
Else
.Offset(RowCount, 48).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 49).Value = "SAM"
Else
.Offset(RowCount, 49).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 50).Value = "MO AMI"
Else
.Offset(RowCount, 50).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 51).Value = "MO GM AT5"
Else
.Offset(RowCount, 51).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 52).Value = "Interventie Coördinatoren"
Else
.Offset(RowCount, 52).Value = ""
End If
.Offset(RowCount, 3).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
    If Me.TxtNaam.Value = "" Then
    MsgBox "Vul alsjeblieft je naam in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.TxtNaam.SetFocus
    Exit Sub
End If
    If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If
    Unload Me
End Sub
Private Sub cmdsluiten_Click()
    Unload Me
End Sub

What part of the code resolved it?

Thanks
 
Upvote 0
Just kind of curious, why do you go through all the checkboxes before you check the name and idea have been entered?

Wouldn't it be better to do it right at the start?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA user form and barcode scannig problem
Replies
1
Views
455
bobsan42
bobsan42
J
  • Solved
Can't get listview to update after second form inputs data
Replies
1
Views
1K
j_tyd
J
D
  • Solved
Shift cell fill color daily
Replies
3
Views
393
dreid1011
dreid1011
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
929
AndyEd
A
S
  • Question
Un-protecting Word Doc by Excel VBA for fillin and upload data to workbook automatically
Replies
0
Views
569
SqnLdrMona
S

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top