commandbutton_click problem
 

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub


Can somone help?

Thank you,

Daniel

commandbutton_click problem
 

If you put a commandbutton from the control toolbox toolbar on a new worksheet
and double click on it, you'll see this procedu

Private Sub CommandButton1_Click()

You can't just add more passed parms to these built in procedures.

But if you were using Sh to represent the worksheet with the commandbutton, you
could just use me.

me.range("a1")....
If the button is on the Report worksheet, you could use:
me.unprotect

But I'm confused on what you're doing with Target.



Daniel R. Young wrote:

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Can somone help?

Thank you,

Daniel

--

Dave Peterson

commandbutton_click problem
 

The target is because I have the cells around a singel cell gray (interior),
so when I click on the macro I would like those to become white.

"Dave Peterson" wrote:

If you put a commandbutton from the control toolbox toolbar on a new worksheet
and double click on it, you'll see this procedu

Private Sub CommandButton1_Click()

You can't just add more passed parms to these built in procedures.

But if you were using Sh to represent the worksheet with the commandbutton, you
could just use me.

me.range("a1")....
If the button is on the Report worksheet, you could use:
me.unprotect

But I'm confused on what you're doing with Target.



Daniel R. Young wrote:

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Can somone help?

Thank you,

Daniel

--

Dave Peterson

commandbutton_click problem
 

I am getting a new error now: Run-time error '13': Type mismatch with this
new code:

Private Sub Commandbutton1_click()
If ActiveSheet.Range("A1:J9769") = "No Photo" Then
Selection.Font.ColorIndex = 2
Selection(-3, 2).Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End If
End Sub


"Dave Peterson" wrote:

If you put a commandbutton from the control toolbox toolbar on a new worksheet
and double click on it, you'll see this procedu

Private Sub CommandButton1_Click()

You can't just add more passed parms to these built in procedures.

But if you were using Sh to represent the worksheet with the commandbutton, you
could just use me.

me.range("a1")....
If the button is on the Report worksheet, you could use:
me.unprotect

But I'm confused on what you're doing with Target.



Daniel R. Young wrote:

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Can somone help?

Thank you,

Daniel

--

Dave Peterson

commandbutton_click problem
 

Then you should dump the Target stuff and just use selection.



Daniel R. Young wrote:

The target is because I have the cells around a singel cell gray (interior),
so when I click on the macro I would like those to become white.

"Dave Peterson" wrote:

If you put a commandbutton from the control toolbox toolbar on a new worksheet
and double click on it, you'll see this procedu

Private Sub CommandButton1_Click()

You can't just add more passed parms to these built in procedures.

But if you were using Sh to represent the worksheet with the commandbutton, you
could just use me.

me.range("a1")....
If the button is on the Report worksheet, you could use:
me.unprotect

But I'm confused on what you're doing with Target.



Daniel R. Young wrote:

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Can somone help?

Thank you,

Daniel

--

Dave Peterson


--

Dave Peterson

commandbutton_click problem
 

A range, eg ActiveSheet.Range("A1:J9769"), can't equal a string, eg "No
Photo". What are you trying to do here?

Rowan

PS Selection(-3, 2).Interior.ColorIndex = 2 will also fail. Probably
should be Selection.offset(-3, 2).Interior.ColorIndex = 2

Daniel R. Young wrote:
I am getting a new error now: Run-time error '13': Type mismatch with this
new code:

Private Sub Commandbutton1_click()
If ActiveSheet.Range("A1:J9769") = "No Photo" Then
Selection.Font.ColorIndex = 2
Selection(-3, 2).Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End If
End Sub


"Dave Peterson" wrote:


If you put a commandbutton from the control toolbox toolbar on a new worksheet
and double click on it, you'll see this procedu

Private Sub CommandButton1_Click()

You can't just add more passed parms to these built in procedures.

But if you were using Sh to represent the worksheet with the commandbutton, you
could just use me.

me.range("a1")....
If the button is on the Report worksheet, you could use:
me.unprotect

But I'm confused on what you're doing with Target.



Daniel R. Young wrote:

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Can somone help?

Thank you,

Daniel

--

Dave Peterson

commandbutton_click problem
 

I have a very long survey that has been created into a report in excel. When
a person wants to print out the document, I would like them to click the
macro button "print report", then all cells that say no photo will change
font color from blue to white (appear hidden) and the cells around them that
are gray will turn to white. Finally I would like all the visible rows (some
rows are hidden) to copy and paste into a word docuement.

I hope this makes sense.


"Rowan" wrote:

A range, eg ActiveSheet.Range("A1:J9769"), can't equal a string, eg "No
Photo". What are you trying to do here?

Rowan

PS Selection(-3, 2).Interior.ColorIndex = 2 will also fail. Probably
should be Selection.offset(-3, 2).Interior.ColorIndex = 2

Daniel R. Young wrote:
I am getting a new error now: Run-time error '13': Type mismatch with this
new code:

Private Sub Commandbutton1_click()
If ActiveSheet.Range("A1:J9769") = "No Photo" Then
Selection.Font.ColorIndex = 2
Selection(-3, 2).Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End If
End Sub


"Dave Peterson" wrote:


If you put a commandbutton from the control toolbox toolbar on a new worksheet
and double click on it, you'll see this procedu

Private Sub CommandButton1_Click()

You can't just add more passed parms to these built in procedures.

But if you were using Sh to represent the worksheet with the commandbutton, you
could just use me.

me.range("a1")....
If the button is on the Report worksheet, you could use:
me.unprotect

But I'm confused on what you're doing with Target.



Daniel R. Young wrote:

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Can somone help?

Thank you,

Daniel

--

Dave Peterson

commandbutton_click problem
 

Hi Rowan,

PS Selection(-3, 2).Interior.ColorIndex = 2 will also fail.

Why should this fail?


---
Regards,
Norman



"Rowan" wrote in message
...
A range, eg ActiveSheet.Range("A1:J9769"), can't equal a string, eg "No
Photo". What are you trying to do here?

Rowan

PS Selection(-3, 2).Interior.ColorIndex = 2 will also fail. Probably
should be Selection.offset(-3, 2).Interior.ColorIndex = 2

Daniel R. Young wrote:
I am getting a new error now: Run-time error '13': Type mismatch with
this new code:

Private Sub Commandbutton1_click()
If ActiveSheet.Range("A1:J9769") = "No Photo" Then
Selection.Font.ColorIndex = 2
Selection(-3, 2).Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End If
End Sub


"Dave Peterson" wrote:


If you put a commandbutton from the control toolbox toolbar on a new
worksheet
and double click on it, you'll see this procedu

Private Sub CommandButton1_Click()

You can't just add more passed parms to these built in procedures.

But if you were using Sh to represent the worksheet with the
commandbutton, you
could just use me.

me.range("a1")....
If the button is on the Report worksheet, you could use:
me.unprotect

But I'm confused on what you're doing with Target.



Daniel R. Young wrote:

I am so close to finishing this button but I am getting the error:

Compile error:
Procedure declartion does not match description of event or procedrue
having
the same name.

Here is my code:
Private Sub Commandbutton1_click(ByVal Sh As Object, ByVal Target As
Range)
Worksheets("Report").Unprotect
Dim rng As Range, rngA As Range
If Target.Value("Report").Range("A1:J9769") = "No Photo" Then
Set rng = Target.Offset(-3, -1)
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey
Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Can somone help?

Thank you,

Daniel

--

Dave Peterson

commandbutton_click problem
 

Oops. My mistake. Failed for me as my activecell was in row 1. Thanks
for pointing that out <g.

Norman Jones wrote:
Hi Rowan,


PS Selection(-3, 2).Interior.ColorIndex = 2 will also fail.


Why should this fail?


---
Regards,
Norman

commandbutton_click problem
 

One way would be to loop through each cell in the range something like this:

dim cell as range
For each cell in ActiveSheet.Range("A1:J9769")
if cell.value = "No Photo" Then
cell.Font.ColorIndex = 2
if cell.row 3 then
Selection(-3, 2).Interior.ColorIndex = 2
end if
end if
next cell

this may be quite slow given the size of the range so another option may
be to apply conditional formatting eg:

With Range("A1:J9769")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""No Photo"""
.FormatConditions(1).Font.ColorIndex = 2
End With

Regards
Rowan


Daniel R. Young wrote:
I have a very long survey that has been created into a report in excel. When
a person wants to print out the document, I would like them to click the
macro button "print report", then all cells that say no photo will change
font color from blue to white (appear hidden) and the cells around them that
are gray will turn to white. Finally I would like all the visible rows (some
rows are hidden) to copy and paste into a word docuement.

I hope this makes sense.


"Rowan" wrote:


A range, eg ActiveSheet.Range("A1:J9769"), can't equal a string, eg "No
Photo". What are you trying to do here?

Rowan

PS Selection(-3, 2).Interior.ColorIndex = 2 will also fail. Probably
should be Selection.offset(-3, 2).Interior.ColorIndex = 2

Daniel R. Young wrote:

I am getting a new error now: Run-time error '13': Type mismatch with this
new code:

Private Sub Commandbutton1_click()
If ActiveSheet.Range("A1:J9769") = "No Photo" Then
Selection.Font.ColorIndex = 2
Selection(-3, 2).Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End If
End Sub