Macro Help

D

David

I am using a macro Simon wrote. Its written to format an address fiel
depending upon a cell value. This macro execute randomly. I would lik
to convert it to a button click. So the user can execute it themselve
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 = "Re: " & 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
 
S

Simon Lloyd

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

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

Thanks for all your help
Code
-------------------
Sub item1(ByVal Target As Range
If Target.Cells.Count > 1 Then Exit Su
If Target.Address <> "$H$12" Then Exit Su
If Me.Range("H12") <> "" The
With Sheets("Letter1"
.Range("B67").Value = Me.Range("H11").Valu
.Range("B68").Value = "Re: " & Me.Range("H6").Valu
.Range("B69").Value = Me.Range("H12").Valu
.Range("B70").Value = Me.Range("a600").Value & "
.Range("B70").Value = Me.Range("H13").Value & ", "
& Me.Range("I13").Value & ", "
& Me.Range("J13").Valu
End Wit
Els
With Sheets("Letter1"
.Range("B67").Value = Me.Range("H6").Valu
.Range("B68").Value = Me.Range("H9").Valu
.Range("B69").Value = Me.Range("H10").Value & ", "
& Me.Range("I10").Value & ", "
& Me.Range("J10").Valu
.Range("B70").Value = Me.Range("a600").Value & "
End Wit
End I
End Su
-------------------
Click to expand...

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
D

David

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

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

Simon Lloyd

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

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
D

David

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

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


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 = "Re: " & 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
 
S

Simon Lloyd

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

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
S

Simon Lloyd

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 = "Re: " & 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
 
D

David

Simon said:
Add this to your button code: Code:
--------------------
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 = "Re: " & 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 --------------------
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro don’t work 4
Date Stamp 4
Ctrl-Alt-Shift-F9 1
Help to modify VBA script so it displays results in other sheet ce 2
Help with combining/merging code 1
Word macro that can call cells out of Excel for a find and replace 0
Macro help 0
Macro code 0

Top