Hi all,

Can anybody provide some code (as I am still new to this) that will execute
the following tasks

1) call the private macro called Private Sub CommandButton1_Click()
2) take the result from the above macro (which would be placed in the next
available row in column B in Sheet 1) and place it into the next available
row in column A of DATA worksheet
3) run the frmNewJob userform

any help appreciated and thanks in advance

"Anthony" wrote in message
...
Hi all,

Can anybody provide some code (as I am still new to this) that will
execute
the following tasks

Please let us know what version of Excel you are using


1) call the private macro called Private Sub CommandButton1_Click()

How do you want to execute the macro? From a toolbar button? From a shortcut
key? From the macro menu?

2) take the result from the above macro (which would be placed in the next
available row in column B in Sheet 1) and place it into the next available
row in column A of DATA worksheet

Post the code you are using, so folks here can help you.

3) run the frmNewJob userform

frmNewJob.show


any help appreciated and thanks in advance

Hi Keith
Sorry for not explaining in more detail, but here is more info..
1) I want to execute the macro using a button
2) here is the code to generate my required 'number'

Private Sub CommandButton1_Click()

' get last ref used
For i = 2 To 9999
R = "B" & i
ref = Range(R)
If ref = "" Then
Exit For
Else
End If
Next i
i = i - 1
dtr = "AA" & i
rer = "AB" & i
ldu = Range(dtr) ' last date used
lru = Range(rer) ' last ref used
dt = Format(Date, "ddmmyy") ' get todays date

' set new ref
If ldu = dt Then ' check if date is the same
nrn = lru + 1 ' date is the same
Else
nrn = 1 ' date is not the same start new ref
End If

i = i + 1
dtr = "AA" & i
rer = "AB" & i
Range(dtr) = dt
Range(rer) = nrn
dref = dt & nrn
ref = "B" & i
Range(ref) = dref

End Sub

3) where do I place the frmNewJob.show into my new code?

Hope this helps

"Keith R" wrote:


"Anthony" wrote in message
...
Hi all,

Can anybody provide some code (as I am still new to this) that will
execute
the following tasks

Please let us know what version of Excel you are using


1) call the private macro called Private Sub CommandButton1_Click()

How do you want to execute the macro? From a toolbar button? From a shortcut
key? From the macro menu?

2) take the result from the above macro (which would be placed in the next
available row in column B in Sheet 1) and place it into the next available
row in column A of DATA worksheet

Post the code you are using, so folks here can help you.

3) run the frmNewJob userform

frmNewJob.show


any help appreciated and thanks in advance

"Anthony" wrote in message
...
Hi Keith
Sorry for not explaining in more detail, but here is more info..
1) I want to execute the macro using a button

You can create a button from the controls toolbar and stick it on the sheet,
although if you do, (a) you need to make sure that it has the right name to
call your procedure (CommandButton1), and (b) I'd suggest making sure that
it doesn't take focus on click, and depending on your needs, you may want to
set it to not be printable (for example, if the form is an invoice or
something that has to look nice). Place the button on the form and while in
design mode, double click it to open the VBE. If it comes up as something
other than Private Sub CommandButton1_Click(), then change the first line of
your macro accordingly (and delete the empty one that it just created when
you double clicked the button).

Assuming Excel2003 or older, you can create a custom toolbar and button
(right click in the toolbar area, click 'customize', toolbar tab, click the
'new' button) and it will give you a blank toolbar. then click the commands
tab, select macro from the list in the left box, then drag the custom button
onto your new toolbar. The first time you use it, it will ask you what macro
you want to associate with the button.

2) here is the code to generate my required 'number'

There are more eloquent ways to do this, but for speed let's just fix what
is critical. The key is that you want to move from column B to column A for
your output, so you need to change each reference to "B" (by itself, not
"AB") to "A". Noted below in two places:


Private Sub CommandButton1_Click()

' get last ref used
For i = 2 To 9999
R = "B" & i '--- change to R = "A" & i
ref = Range(R)
If ref = "" Then
Exit For
Else
End If
Next i
i = i - 1
dtr = "AA" & i
rer = "AB" & i
ldu = Range(dtr) ' last date used
lru = Range(rer) ' last ref used
dt = Format(Date, "ddmmyy") ' get todays date

' set new ref
If ldu = dt Then ' check if date is the same
nrn = lru + 1 ' date is the same
Else
nrn = 1 ' date is not the same start new ref
End If

i = i + 1
dtr = "AA" & i
rer = "AB" & i
Range(dtr) = dt
Range(rer) = nrn
dref = dt & nrn
ref = "B" & i '-----ref = "A" & i
Range(ref) = dref

End Sub

3) where do I place the frmNewJob.show into my new code?

That is difficult to determine without more information; what information
are you collecting with frmNewJob? There are no references to the userform
in this code that you provided.

Go into the VBE, find the userform (post again if you aren't sure what I'm
talking about), and right click on the userform and select "view code". Go
ahead and post that here as well as any general information you can provide
about what your userform is supposed to do; does it allow raw data entry
(instead of hand entering data on your sheet), etc.

Best,
Keith


Hope this helps

"Keith R" wrote:


"Anthony" wrote in message
...
Hi all,

Can anybody provide some code (as I am still new to this) that will
execute
the following tasks

Please let us know what version of Excel you are using


1) call the private macro called Private Sub CommandButton1_Click()

How do you want to execute the macro? From a toolbar button? From a
shortcut
key? From the macro menu?

2) take the result from the above macro (which would be placed in the
next
available row in column B in Sheet 1) and place it into the next
available
row in column A of DATA worksheet

Post the code you are using, so folks here can help you.

3) run the frmNewJob userform

frmNewJob.show


any help appreciated and thanks in advance

Keith,
Just quick note to say thanks for your efforts/help - all seems to be
working fine!
cheers
Anthony

"Keith R" wrote:


"Anthony" wrote in message
...
Hi Keith
Sorry for not explaining in more detail, but here is more info..
1) I want to execute the macro using a button

You can create a button from the controls toolbar and stick it on the sheet,
although if you do, (a) you need to make sure that it has the right name to
call your procedure (CommandButton1), and (b) I'd suggest making sure that
it doesn't take focus on click, and depending on your needs, you may want to
set it to not be printable (for example, if the form is an invoice or
something that has to look nice). Place the button on the form and while in
design mode, double click it to open the VBE. If it comes up as something
other than Private Sub CommandButton1_Click(), then change the first line of
your macro accordingly (and delete the empty one that it just created when
you double clicked the button).

Assuming Excel2003 or older, you can create a custom toolbar and button
(right click in the toolbar area, click 'customize', toolbar tab, click the
'new' button) and it will give you a blank toolbar. then click the commands
tab, select macro from the list in the left box, then drag the custom button
onto your new toolbar. The first time you use it, it will ask you what macro
you want to associate with the button.

2) here is the code to generate my required 'number'

There are more eloquent ways to do this, but for speed let's just fix what
is critical. The key is that you want to move from column B to column A for
your output, so you need to change each reference to "B" (by itself, not
"AB") to "A". Noted below in two places:


Private Sub CommandButton1_Click()

' get last ref used
For i = 2 To 9999
R = "B" & i '--- change to R = "A" & i
ref = Range(R)
If ref = "" Then
Exit For
Else
End If
Next i
i = i - 1
dtr = "AA" & i
rer = "AB" & i
ldu = Range(dtr) ' last date used
lru = Range(rer) ' last ref used
dt = Format(Date, "ddmmyy") ' get todays date

' set new ref
If ldu = dt Then ' check if date is the same
nrn = lru + 1 ' date is the same
Else
nrn = 1 ' date is not the same start new ref
End If

i = i + 1
dtr = "AA" & i
rer = "AB" & i
Range(dtr) = dt
Range(rer) = nrn
dref = dt & nrn
ref = "B" & i '-----ref = "A" & i
Range(ref) = dref

End Sub

3) where do I place the frmNewJob.show into my new code?

That is difficult to determine without more information; what information
are you collecting with frmNewJob? There are no references to the userform
in this code that you provided.

Go into the VBE, find the userform (post again if you aren't sure what I'm
talking about), and right click on the userform and select "view code". Go
ahead and post that here as well as any general information you can provide
about what your userform is supposed to do; does it allow raw data entry
(instead of hand entering data on your sheet), etc.

Best,
Keith


Hope this helps

"Keith R" wrote:


"Anthony" wrote in message
...
Hi all,

Can anybody provide some code (as I am still new to this) that will
execute
the following tasks

Please let us know what version of Excel you are using


1) call the private macro called Private Sub CommandButton1_Click()

How do you want to execute the macro? From a toolbar button? From a
shortcut
key? From the macro menu?

2) take the result from the above macro (which would be placed in the
next
available row in column B in Sheet 1) and place it into the next
available
row in column A of DATA worksheet

Post the code you are using, so folks here can help you.

3) run the frmNewJob userform

frmNewJob.show


any help appreciated and thanks in advance

1) Call Worksheet_name.CommandButton1_Click, and you will have to make it a
Public Sub.

2) A Sub doesn't return a result, so best to change that procedure to do it

Me.Cells(Me.Rows.Count,"B").End(xlUp).Offset(1,0). Value = the_value

3) frmNewJob.Show

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Anthony" wrote in message
...
Hi all,

Can anybody provide some code (as I am still new to this) that will
execute
the following tasks

1) call the private macro called Private Sub CommandButton1_Click()
2) take the result from the above macro (which would be placed in the next
available row in column B in Sheet 1) and place it into the next available
row in column A of DATA worksheet
3) run the frmNewJob userform

any help appreciated and thanks in advance