Error when Printing from a validation list 1

[Wray69]

I am trying to print each change in a validation list by using the following code;

CODE
Sub Print_all()
ActiveSheet.PrintOut
For Each c In Range("names")
[J5] = C.Value
ActiveSheet.PrintOut
Next
End Sub
When I attempt to run the code I am getting the following errors;

un-time error 1004 application defined or object defined error

or

method range of object worksheet failed

Anyone have any ideas?

Thanks,

Wray

 

[KenPuls]

Hi there,

The following code will read the validation list from a cell, whether it is a typed in list or a reference to a range. Hopefully this gives you enough of an idea on how to access it. If not, post back and I'll try to help, although I'm not quite clear on what you're trying to do.

Sub ReadValidation()
Dim cl As Range
Dim strMsg As String
Dim lIndex As Long

Dim rngValid() As Variant
Dim strValid() As String

Set cl = ActiveCell
On Error GoTo ExitOut

If InStr(1, cl.Validation.Formula1, "=") = 1 Then
    'Must be a cell reference
    rngValid = Range(Right(cl.Validation.Formula1, Len(cl.Validation.Formula1) - 1))
    For lIndex = LBound(rngValid) To UBound(rngValid)
        strMsg = strMsg & vbNewLine & rngValid(lIndex, 1)
    Next lIndex
Else
    'Comma separated list
    strValid = Split(cl.Validation.Formula1, ",")
    For lIndex = LBound(strValid) To UBound(strValid)
        strMsg = strMsg & vbNewLine & strValid(lIndex)
    Next lIndex
End If

MsgBox "Your validation contains the following list" & vbNewLine & _
    strMsg

ExitOut:

End Sub

Ken Puls, CMA

http://www.excelguru.ca

 

[Wray69]

Thanks for the reply. Didnt really help me any though.

What I have is a form and when you change the value in the validation list it populates different values in that form. There is about 200 entries in the validation list and I need to print each form.

I hace used a code similar to the one I have above on a different workbook and it worked fine but I am unable to get it to work on this one.

Any Ideas?

Thanks,

Wray

 

[KenPuls]

When you say form, are you talking about a custom userform, a word document or Excel spreasheet.

I gave you code to actually deal with Excel Validation object. I'm wondering if your validation is actually not truly using the validation feature, but rather something else.

Can you explain how it's set up a little more?

Ken Puls, CMA

http://www.excelguru.ca

 

[Wray69]

It is an excel spreadsheet and I am using a validation list. When you change the validation value there are vlookups that pull up the related data.

I Need to print each value change. I hope this is a little clearer.

Thanks,

Wray

 

[KenPuls]

Okay, so just to be sure...

Each time someone changes the value from the validation list, you want to print the sheet? Is that it?

Say, for example, your validation list is in J5. When they change that value, you want to print out the entire page?

Ken Puls, CMA

http://www.excelguru.ca

 

[Wray69]

Pretty much but I dont want to manually change the validation list. I want to be able to click a command button and have it go through and print out all the sheets.

Thanks,

Wray

 

[KenPuls]

Okay, let's try this:

Sub PrintValidation()
Dim cl As Range
Dim lIndex As Long

Dim rngValid() As Variant
Dim strValid() As String

Set cl = ActiveCell
On Error GoTo ExitOut

If InStr(1, cl.Validation.Formula1, "=") = 1 Then
    'Must be a cell reference
    rngValid = Range(Right(cl.Validation.Formula1, Len(cl.Validation.Formula1) - 1))
    For lIndex = LBound(rngValid) To UBound(rngValid)
        cl.Value = rngValid(lIndex, 1)
        cl.Parent.PrintOut copies:=1
    Next lIndex
Else
    'Comma separated list
    strValid = Split(cl.Validation.Formula1, ",")
    For lIndex = LBound(strValid) To UBound(strValid)
        cl.Value = rngValid(lIndex)
        cl.Parent.PrintOut copies:=1
    Next lIndex
End If

ExitOut:

End Sub

Put your cursor in the cell that has the validation list and run it. I haven't tested this myself, but I think it should work for you. Please let me know.

Ken Puls, CMA

http://www.excelguru.ca

 

[Wray69]

Doesn't appear to be doing anything.

 

[KenPuls]

Can you tell me what the cell address is that has the validation setting? Also, if you go into Data|Validation, what is in the Source field?

Ken Puls, CMA

http://www.excelguru.ca

 

[Wray69]

The cell address is J5 and and the Source in Data|Validation is =names

Thank You,

Greg

 

[KenPuls]

Weird,

I just mocked up a similar scenario here. I changed the reference to the specific cell, and it worked fine.

Try this:

Sub PrintValidation()
Dim cl As Range
Dim lIndex As Long

Dim rngValid() As Variant
Dim strValid() As String

Set cl = ActiveSheet.Range("J5")
On Error GoTo ExitOut

If InStr(1, cl.Validation.Formula1, "=") = 1 Then
    'Must be a cell reference
    rngValid = Range(Right(cl.Validation.Formula1, Len(cl.Validation.Formula1) - 1))
    For lIndex = LBound(rngValid) To UBound(rngValid)
        cl.Value = rngValid(lIndex, 1)
        cl.Parent.PrintOut copies:=1
    Next lIndex
Else
    'Comma separated list
    strValid = Split(cl.Validation.Formula1, ",")
    For lIndex = LBound(strValid) To UBound(strValid)
        cl.Value = rngValid(lIndex)
        cl.Parent.PrintOut copies:=1
    Next lIndex
End If

ExitOut:

End Sub

Ken Puls, CMA

http://www.excelguru.ca

 

[Wray69]

Thanks for all your help but I dont know what is going wrong, I am still getting nothing. Are there any settings that would stop this from running?

Thanks,

Wray

 

[KenPuls]

Are you actually running the macro? This needs to be triggered manually and will print all the sheets for you at once...

Ken Puls, CMA

http://www.excelguru.ca

 

[Wray69]

OK, now it is working for no apparent reason. I thank you for all your help...


Wray

 

[KenPuls]

LOL! Glad to hear it! I was running out of things to try!

Cheers,

Ken Puls, CMA

http://www.excelguru.ca