Solved: CommandButton on a worksheet

[mike31z]

I need a little help in creating a VBA code for a command button, I want to use on a small spreadsheet. If some on can help with the instructions for a couple of cell I can figure out the rest.

I have reduced the spreadsheet to two tabs and removed all the modules.

I have a UserForm that has done the same thing but its time to evolve and want to add more options to the data base.

spreadsheet is attached.


Mike in wisconsin :cool:

Thank for looking
</IMG>

[Simon Lloyd]

Mike i haven't looked ta your attachment as nothing in your post enticed me to, could you explain what you would like to achieve?

[lucas]

Why not use a userform. It's just as easy and looks much better and you can be on the same page as the database while running it if you so desire.

see attached.

[mike31z]

There are a couple of things. One of the cells will have all the data that is located on our membership card that has a magnetic strip. from that cell I can extract the membership number and name. there is also the member address but that minor, I can extract that by formula once I get the long string posted to the Reg Tab.

I will also print the registration information for the participant so the can see which events they are signed up to participate in. This is easy when the registration cells are on a work sheet vs a userform

We will be using a magnetic card reader that when the card is swiped all the data will go in one cell (the active cell).

It easy to use the UserForm to post the registration input data but its hard to extract the member membership from within the UserForm.

my early attempts to modify the VBA that I use on the USERform commandbutton failed because I don't know enough about VBA.

[mike31z]

Lucas, I already have a userform that works good but its time to evolve and use a magnetic card reader. I can not figure out how to extract data from the long string of data that comes from the card reader and populate 3 to 4 fields on the userform and then post all the text box on the userform by the commandbutton. I can get the long string into a text box but that it. not extract the name, address, membership number.


mike in WI, USA

[lucas]

Ok Mike, I'll be back to look at this again with you as soon as I have the time.....probably tommorrow.

[Simon Lloyd]

This will get you going....it will copy the data from the first two blocks for you, have a go at the rest, post back of you need help!
Sub comButton()
Dim i As Long
Dim Rng As Range, Rng1 As Range, MyCell As Range, oCell As Range
With Sheets("Invoice")
.Range("F7").Copy
Sheets("Reg").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("F8").Copy
Sheets("Reg").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Set Rng = .Range("F10:F14")
Set Rng1 = Sheets("Reg").Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
For i = 0 To 4
.Range("F" & 10 + i).Copy
Rng1.Offset(0, i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next i
End With
End Sub

[lucas]

I would have done it a little differently, same as the userform example:
Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Object
Dim response As String
Set LastRow = Sheets("Reg").Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = Sheets("Invoice").Range("F7").Value
LastRow.Offset(1, 2).Value = Sheets("Invoice").Range("F8").Value
LastRow.Offset(1, 3).Value = Sheets("Invoice").Range("F10").Value
LastRow.Offset(1, 4).Value = Sheets("Invoice").Range("F11").Value
response = MsgBox("Do you want to clear the form?", _
vbYesNo)
If response = vbYes Then
Me.Range("F7").Value = ""
Me.Range("F8").Value = ""
Me.Range("F10").Value = ""
Me.Range("F11").Value = ""
Me.Range("F7").Select
End If
End Sub

[Simon Lloyd]

No problem with that Steve, it looks neater anyway!, however, simply for compatibility issues rather than Set LastRow = Sheets("Reg").Range("a65536").End(xlUp)
i would go for Set LastRow = Sheets("Reg").Range("A" & Rows.Count).End(xlUp)
Mike, sometimes its better the devil you know!, as Steve has pointed out, less code, easier to understand, less to go wrong!

[lucas]

Good catch on the rows. count Simon. I still have 2003 so I haven't fully complied with the latest as I should.

I just hope the pastespecial isn't a requirement for some reason. So far, from his workbook I don't see why it would be necessary.

[Simon Lloyd]

There's no special need for pastespecial lol! (you will hate xl2007...don't upgrade!), the code i gave was because he stated that he didn't want to continue with a userform.

[mike31z]

Simon and Lucas
Thank you very much everytime I ask for help its been provided and I also learn more and more. Lucas thanks for adding the clear form that helped and adding the code to the spreadsheet. It work on my computer.

I still use Office 2000 and we will use this 8 or so times in 2009 but it will make the sport registration process go a little smoother.

Thank You


Mike in Wisconsin