Select case is not working after values copied from other worksheets

[Space_Maniak]

Good afternoon,

I would like to ask you guys for a help. I am using Application Intersect with Select Case to enable dynamic hiding of selected rows based of inserted value to certain field (the point is to make formular as small as possible for tje user). The code looks like this. I repeat this part for tens of fields (i am a beginner so didnt know how else to do it).

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("C46"), Range(Target.Address)) Is Nothing Then 
        Select Case Target.Value
        Case Is = "ANO"
                                
        Rows("47:49").EntireRow.Hidden = False
   
        Case Is = "NE"
        
        Rows("47:49").EntireRow.Hidden = True
        Range("C47:C49") = ""
        
        Case Is = ""
        
        Rows("47:49").EntireRow.Hidden = True
        
         End Select
         End If
End Sub

Problem is that i need to pull data from other sheet but it ll error and stop the macro above. I am using simple fomulation like this:

Sub odemčení()

pass = InputBox("Zadejte heslo:")

ThisWorkbook.Unprotect pass

Worksheets("Formulář_klient").Range("C18:C25").Value = Worksheets("Formulář").Range("C39:C46").Value

End Sub

But after the value is transfered i've got this message:

Does anybody know what the problem is and how to solve it?

Thanks in advance!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C46")) Is Nothing Then Exit Sub
     Select Case Target.Value
         Case "ANO"
            Rows("47:49").EntireRow.Hidden = False
         Case "NE"
            Rows("47:49").EntireRow.Hidden = True
            Range("C47:C49") = ""
         Case ""
            Rows("47:49").EntireRow.Hidden = True
    End Select
End Sub

 

[Space_Maniak]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C46")) Is Nothing Then Exit Sub
     Select Case Target.Value
         Case "ANO"
            Rows("47:49").EntireRow.Hidden = False
         Case "NE"
            Rows("47:49").EntireRow.Hidden = True
            Range("C47:C49") = ""
         Case ""
            Rows("47:49").EntireRow.Hidden = True
    End Select
End Sub

I have tried. Error goes away but the funkcionality of hiding too. So it does not help.

Thank you anyways.

 

[mumps]

It works for me. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Fluff]

How is C46 being changed?

 

[Space_Maniak]

Thanky all. I appears i have solved the problem. I am not sure why but if i copy range and one of the cells is the one which is used for intersection ("C46" in my case) "type mismatch" error occurs. But if copy divide range between cells which are not used and all cells like "C46" are selected one by one. See an example:

Sub nakopírování()
    Dim wb As Workbook, wb2 As Workbook
    Dim ws As Worksheet
    Dim vFile As Variant

    'Set source workbook
    Set wb2 = ActiveWorkbook
    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xlsm", _
        1, "Select One File To Open", , False)
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
    'Set targetworkbook
    Set wb = ActiveWorkbook

    'For instance, copy data from a range in the first workbook to another range in the other workbook
    wb2.Worksheets("Formulář").Range("C46:C52").Value = wb.Worksheets("Formulář_klient").Range("C18:C24").Value
    wb2.Worksheets("Formulář").Range("C53:C53").Value = wb.Worksheets("Formulář_klient").Range("C25:C25").Value
    
    'wb.Close
    
End Sub

Also thank you for your tips how to proceedwith my posts.

 

[Space_Maniak]

Thanky all. I appears i have solved the problem. I am not sure why but if i copy range and one of the cells is the one which is used for intersection ("C46" in my case) "type mismatch" error occurs. But if copy divide range between cells which are not used and all cells like "C46" are selected one by one. See an example:

Sub nakopírování()
    Dim wb As Workbook, wb2 As Workbook
    Dim ws As Worksheet
    Dim vFile As Variant

    'Set source workbook
    Set wb2 = ActiveWorkbook
    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xlsm", _
        1, "Select One File To Open", , False)
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
    'Set targetworkbook
    Set wb = ActiveWorkbook

    'For instance, copy data from a range in the first workbook to another range in the other workbook
    wb2.Worksheets("Formulář").Range("C46:C52").Value = wb.Worksheets("Formulář_klient").Range("C18:C24").Value
    wb2.Worksheets("Formulář").Range("C53:C53").Value = wb.Worksheets("Formulář_klient").Range("C25:C25").Value
   
    'wb.Close
   
End Sub

Also thank you for your tips how to proceedwith my posts.

Cell 53 contains list of YES and NO which defines which rows should be hidden and which not. So f i copy the value separated from the rest of the range the problem is gone.

 

[mumps]

Glad it worked out.