Code not working properly

[Nadine1988]

Hello Excel Experts,
i have one last error within my file related to the following code:

Sub Optionsfeld38_BeiKlick()
Sheet1.Unprotect Password:="Vizrt"

If Optionsfeld38 = False Then
   Rows("15:16").EntireRow.Hidden = Not Rows("15:16").EntireRow.Hidden
End If
        
Sheet1.Protect Password:="Vizrt"

End Sub
Sub Optionsfeld39_BeiKlick()
Sheet1.Unprotect Password:="Vizrt"

    Rows("15:16").EntireRow.Hidden = False

Sheet1.Protect Password:="Vizrt"
End Sub

So the code actually works - it's supposed to hide rows 15:16 depending on the choice in the options field.
The strange thing which is not working is that it unhides the row's as soon as i continue to fill out my excel form.

Can this be related to the protect and unprotect part of the code?
Or any other ideas?

Thanks
Nadine

 

[Micron]

Sound like you are calling either of those subs with the wrong event. Perhaps you're using a Change event on a userform? Move the calling code to either a command button or an update event. Can't say which because you haven't really given enough info about what calls the code and what you're doing to cause it to be called.

Other ideas?
1) use one control (can't tell if you're using checkboxes or something else) to hide unhide. If it's checked do one; if unchecked do the other. You can cut out half of that code.
2) use one command button. If the caption is "Hide" then hide when clicked and edit the caption to Unhide. Do one or the other based on the caption.

 

[Nadine1988]

The code is called on from the option buttons. I do have two - one saying "Yes" the other saying "no" - it needs to be hidden if it's No and unhidden if its yes. I've now grouped the buttons and adjusted the code to the following :

Sub Optionsfeld38_BeiKlick()
Sheet1.Unprotect Password:="Vizrt"

If Optionsfeld38 = False Then
   Rows("15:16").EntireRow.Hidden = Not Rows("15:16").EntireRow.Hidden
Else
    Rows("15:16").EntireRow.Hidden = False

End If
        
Sheet1.Protect Password:="Vizrt"

End Sub

The code itself is working but it is changing to unhide whenever any other entries are made in the sheet.

 

[Micron]

Perhaps it fires when editing sheet cells because you have other event code such as sheet change or activate events. Sometimes such code causes other code to run. If you have those events you could try inserting Application.EnableEvents = False (and True at the end to turn that back on). I recommend using an error handler if altering application settings lest they remain altered if the code errs out. Sometimes a global variable is needed to stop other code from running.

 

[Nadine1988]

okay thanks - that's to much for me. i think I have to go back to the beginning and start again. I'm really new in the VBA world and i have to admit that everything I've made so far is working more or less out of luck... but thanks for your time and your help!

 

[Nadine1988]

I've just realized that not even my code is working properly anymore.

 

[Micron]

Well, if you want to upload your wb to a file share somewhere I could probably take a look at it for you, but the language might be an issue for me.

 

[Nadine1988]

i don't see an option to upload a file here?

Well, if you want to upload your wb to a file share somewhere I could probably take a look at it for you, but the language might be an issue for me.

 

[Micron]

You can't. I meant a file share.

 

[Micron]

No, that opens the file. You should use a file share, not give people access to your One Drive. I hope you don't intend to rely on that file as a backup now.
Might be safer for you if I pm you with an email address you can use since file sharing seems to be new to you.