I am using a macro Simon wrote. Its written to format an address field
depending upon a cell value. This macro execute randomly. I would like
to convert it to a button click. So the user can execute it themselves
when they need it. Thanks for all your help.


Code:
--------------------

Sub item1(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address < "$H$12" Then Exit Sub
If Me.Range("H12") < "" Then
With Sheets("2-10000 Order and Cover")
.Range("B67").Value = Me.Range("H11").Value
.Range("B68").Value = " " & Me.Range("H6").Value
.Range("B69").Value = Me.Range("H12").Value
.Range("B70").Value = Me.Range("a600").Value & " "
.Range("B70").Value = Me.Range("H13").Value & ", " _
& Me.Range("I13").Value & ", " _
& Me.Range("J13").Value
End With
Else
With Sheets("2-10000 Order and Cover")
.Range("B67").Value = Me.Range("H6").Value
.Range("B68").Value = Me.Range("H9").Value
.Range("B69").Value = Me.Range("H10").Value & ", " _
& Me.Range("I10").Value & ", " _
& Me.Range("J10").Value
.Range("B70").Value = Me.Range("a600").Value & " "
End With
End If
End Sub

--------------------


--
David
------------------------------------------------------------------------
David's Profile: http://www.thecodecage.com/forumz/member.php?userid=630
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388

Hi David, firstly you cant simply change the name of the sub and still
keep (ByVal Target AS Range) (thats a worksheet event code), what you
can do is change TARGET to activecell and ME to the sheet you are
working from in this format Sheets("Sheet1") get rid of the ByVal
....etc and then assign the macro item1 to your button.

David;456620 Wrote:
I am using a macro Simon wrote. Its written to format an address field
depending upon a cell value. This macro executes randomly. I would like
to convert it to a button click. So the user can execute it themselves
when they need it. I need help in converting this code to a code for a
button (Button266_Click).

Thanks for all your help.


Code:
--------------------

Sub item1(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address < "$H$12" Then Exit Sub
If Me.Range("H12") < "" Then
With Sheets("Letter1")
.Range("B67").Value = Me.Range("H11").Value
.Range("B68").Value = " " & Me.Range("H6").Value
.Range("B69").Value = Me.Range("H12").Value
.Range("B70").Value = Me.Range("a600").Value & " "
.Range("B70").Value = Me.Range("H13").Value & ", " _
& Me.Range("I13").Value & ", " _
& Me.Range("J13").Value
End With
Else
With Sheets("Letter1")
.Range("B67").Value = Me.Range("H6").Value
.Range("B68").Value = Me.Range("H9").Value
.Range("B69").Value = Me.Range("H10").Value & ", " _
& Me.Range("I10").Value & ", " _
& Me.Range("J10").Value
.Range("B70").Value = Me.Range("a600").Value & " "
End With
End If
End Sub

--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388

Simon Lloyd;456643 Wrote:
Hi David, firstly you cant simply change the name of the sub and still
keep (ByVal Target AS Range) (thats a worksheet event code), what you
can do is change TARGET to activecell and ME to the sheet you are
working from in this format Sheets("Sheet1") get rid of the ByVal
....etc and then assign the macro item1 to your button.

Will try it Simon,, I am not to sure if I can pull it off but I will
update this thread. thanks again


--
David
------------------------------------------------------------------------
David's Profile: http://www.thecodecage.com/forumz/member.php?userid=630
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388

David;456734 Wrote:
Will try it Simon,, I am not to sure if I can pull it off but I will
update this thread. thanks againWhen you are in the VBE (Visual Basic Editor) use EDITREPLACEREPLACE
ALL to make your changes that way you are sure they are correct :)


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388

David;456734 Wrote:
Will try it Simon,, I am not to sure if I can pull it off but I will
update this thread. thanks again

I tried the following, no luck the code fires but nothing is
happening., the sheet where I am getting the info is letter1:



Code:
--------------------

Sub Button266_Click()
If ActiveCell.Cells.Count 1 Then Exit Sub
If ActiveCell.Address < "$H$12" Then Exit Sub
Let objForm = 0
If objForm.Range("H12") < "" Then
With Sheets("letter2")
.Range("B67").Value = objForm.Range("H11").Value
.Range("B68").Value = " " & objForm.Range("H6").Value
.Range("B69").Value = objForm.Range("H12").Value
.Range("B70").Value = objForm.Range("a600").Value & " "
.Range("B70").Value = objForm.Range("H13").Value & ", " _
& objForm.Range("I13").Value & ", " _
& objForm.Range("J13").Value
End With
Else
With Sheets("letter2")
Let objForm = "letter2"
.Range("B67").Value = objForm.Range("H6").Value
.Range("B68").Value = objForm.Range("H9").Value
.Range("B69").Value = objForm.Range("H10").Value & ", " _
& objForm.Range("I10").Value & ", " _
& objForm.Range("J10").Value
.Range("B70").Value = objForm.Range("a600").Value & " "
End With
End If
End Sub

--------------------


--
David
------------------------------------------------------------------------
David's Profile: http://www.thecodecage.com/forumz/member.php?userid=630
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388

You cannot just slot obj.form in there for a start the form doesn't have
a range, i stated it must be of this format *Sheets("Sheet1")* why have
you over complicated a simple code with LET....etc, i dont understand?


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388

Add this to your button code

Code
-------------------
Sub Button266_Click(
If ActiveCell.Cells.Count 1 Then Exit Su
If ActiveCell.Address < "$H$12" Then Exit Su
If Sheets("Letter1").Range("H12") < "" The
With Sheets("Letter2"
.Range("B67").Value = Sheets("Letter1").Range("H11").Valu
.Range("B68").Value = " " & Sheets("Letter1").Range("H6").Valu
.Range("B69").Value = Sheets("Letter1").Range("H12").Valu
.Range("B70").Value = Sheets("Letter1").Range("a600").Value & "
.Range("B70").Value = Sheets("Letter1").Range("H13").Value & ", "
& Sheets("Letter1").Range("I13").Value & ", "
& Sheets("Letter1").Range("J13").Valu
End Wit
Els
With Sheets("Letter2"
.Range("B67").Value = Sheets("Letter1").Range("H6").Valu
.Range("B68").Value = Sheets("Letter1").Range("H9").Valu
.Range("B69").Value = Sheets("Letter1").Range("H10").Value & ", "
& Sheets("Letter1").Range("I10").Value & ", "
& Sheets("Letter1").Range("J10").Valu
.Range("B70").Value = Sheets("Letter1").Range("a600").Value & "
End Wit
End I
End Su
-------------------

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=12638

Simon Lloyd;456792 Wrote:
Add this to your button code:

Code:
--------------------
Sub Button266_Click()
If ActiveCell.Cells.Count 1 Then Exit Sub
If ActiveCell.Address < "$H$12" Then Exit Sub
If Sheets("Letter1").Range("H12") < "" Then
With Sheets("Letter2")
.Range("B67").Value = Sheets("Letter1").Range("H11").Value
.Range("B68").Value = " " & Sheets("Letter1").Range("H6").Value
.Range("B69").Value = Sheets("Letter1").Range("H12").Value
.Range("B70").Value = Sheets("Letter1").Range("a600").Value & " "
.Range("B70").Value = Sheets("Letter1").Range("H13").Value & ", " _
& Sheets("Letter1").Range("I13").Value & ", " _
& Sheets("Letter1").Range("J13").Value
End With
Else
With Sheets("Letter2")
.Range("B67").Value = Sheets("Letter1").Range("H6").Value
.Range("B68").Value = Sheets("Letter1").Range("H9").Value
.Range("B69").Value = Sheets("Letter1").Range("H10").Value & ", " _
& Sheets("Letter1").Range("I10").Value & ", " _
& Sheets("Letter1").Range("J10").Value
.Range("B70").Value = Sheets("Letter1").Range("a600").Value & " "
End With
End If
End Sub
--------------------


Thanks it seems to be woking now, I will post back , thanks Simon!


--
David
------------------------------------------------------------------------
David's Profile: http://www.thecodecage.com/forumz/member.php?userid=630
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388

If your query has been solved!


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126388