Code not working properly

Nadine1988

Board Regular
Joined
Jun 12, 2023
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hello Excel Experts,
i have one last error within my file related to the following code:

VBA 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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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 :

VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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. :oops:
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top