commandbutton_click problem

[Daniel R. Young]

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]

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

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]

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.

 

[Daniel R. Young]

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]

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

 

[Rowan]

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]

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.

 

[Norman Jones]

Hi Rowan,

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

Why should this fail?

 

[Rowan]

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

 

[Rowan]

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