Thema Datum  Von Nutzer Rating
Antwort
Rot Zellen sperren/entsperren
28.07.2022 16:00:49 OnlyLookOutside
Solved
28.07.2022 17:55:37 Gast21544
NotSolved
28.07.2022 18:04:01 xlKing
NotSolved
28.07.2022 18:07:19 xlKing
NotSolved
01.08.2022 16:44:11 OnlyLookOutside
NotSolved
01.08.2022 17:40:29 xlKing
NotSolved

Ansicht des Beitrags:
Von:
OnlyLookOutside
Datum:
28.07.2022 16:00:49
Views:
84
Rating: Antwort:
 Nein
Thema:
Zellen sperren/entsperren

Hallo zusammen,

Ziel ist es über eine Zelle bestimmte Zellen zu sperren bzw. zu entsperren. Leider kriege ich immer eine Fehlermeldung. Hat jemand eine idee?

Die Auswahl der 3 Cases läuft über ein Listenauswahlfeld.


Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("$C$15")) Is Nothing Then Exit Sub
    Dim v As Integer
    v = Target.Value
    On Error GoTo Ende
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    Cells.Locked = True
    Select Case v
        Case "Kostenüberschlag +/- 30%"
            Range("H66, J66, O66, C69, F69, J69, O69, C70, F70; H70, J70, O70, C71, F70, J71, O71, H74, J74, O74, H76, J76, O76, H78, J78, O78, H80, J80, O80, H82, J82, O82, H84, J84, O84, H86, J86, O86, H88, J88, O88, H90, J90, O90, H92, J92, O92, H95, J95, O95, H97, J97, O97, H99, J99, O99, H101, J101, O101, H118, J118, O118, H121, J121, O121, H123, J123, O123, H125, J125, O125, H127, J127, O127, H129, J129, O129, H131, J131, O131, H134, J134, O134, H136, J136, O136, H138, J138, O138, H140, J140, O140, H142, J142, O142, H144, J144, O144, H146, J146, O146, H148, J148, O148, H150, J150, O150, H152, J152, O152, H155, J155, O155, H157, J157, O157, H159, J159, O159, H161, J161, O161, H163, J163, O163, H165, J165, O165, H167, J167, O167, H169, J169, O169, H171, J171, O171, H173, J173, O173, H176, J176, O176, H179, J179, O179, H181, J181, O181, H183, J183, O183").Locked = False
            Range("H186, J186, H203, J203, O203, H206, J206, O206, H208, J208, O208, H210, J210, O210, H212, J212, O212, H214, J214, O214, H217, J217, O217, H219, J219, O219, H221, J221, O221, H223, J223, O223, H225, J225, O225, H227, J227, O227, H229, J229, O229, H231, J231, O231, H233, J233, O233, H236, J236, O236, H238, J238, O238, H240, J240, O240, H242, J242, O242, H244, J244, O244, H246, J246, O246, H248, J248, O248, H250, J250, O250, H252, J252, O252, H254, J254, O254, H257, J257, O257, H259, J259, O259, H261, J261, O261, H263, J263, O263, H265, J265, O265, H267, J267, O267, H269, J269, O269, H271, J271, O271, H274, J274, O274, H276, J276, O276, H278, J278, O278, H280, J280, O280, H282, J282, O282, H287, J287, O287, H289, J289, O289, H291, J291, O291, H293, J293, O293, H295, J295, O295, H298, J298, O298, H300, J300, O300, H302, J302, O302").Locked = False
            Range("H304, J304, O304, H307, J307, O307, H309, J309, O309, H311, J311, O311, H313, J313, O313, H315, J315, O315, H317, J317, O317, H319, J319, O319, H321, J321, O321, H323, J323, O323, H325, J325, O325, H328, J328, H344, J344, N344, H347, J347, N347, H349, J349, N349, H351, J351, N351, H353, J353, N353, H355, J355, N355, H358, J358, N358, H360, J360, N360, H362, J362, N362, H364, J364, N364, H366, J366, N366, H369, J369, N369, H371, J371, N371, H373, J373, N373, H375, J375, N375, H377, J377, N377, H379, J379, N379, H382, J382, N382, H384, J384, N384, H386, J386, N386, H388, J388, N388, H390, J390, N390, H392, J392, N392, H394, J394, N394, H396, J396, N396, H399, J399, N399, H401, J401, N401, H403, J403, N403, H405, J405, N405, H407, J407, N407, H409, J409, N409, H411, J411, N411, H413, J413, N413, H415, J415, N415, H418, J418, N418").Locked = False
            Range("H420, J420, N420, H422, J422, N422, H424, J424, N424, H426, J426, N426, H428, J428, N428, H430, J430, N430, H433, J433, N433, H435, J435, N435, H437, J437, N437, H439, J439, N439, H441, J441, N441, H443, J443, N443, H445, J445, N445, H447, J447, N447, H449, J449, N449, H451, J451, N451, H454, J454, N454, H456, J456, N456, H458, J458, N458, H460, J460, N460, H462, J462, N462, H464, J464, N464, H466, J466, N466, H469, J469, N469, H471, J471, N471, H473, J473, N473, H475, J475, N475, H477, J477, N477, H479, J479, N479, H481, J481, N481, H483, J483, N483, H485, J485, N485, H487, J487, N487, H490, J490, H506, J506, O506, H509, J509, O509, H511, J511, O511, H513, J513, O513, H515, J515, O515, H518, J518, O518, H520, J520, O520, H522, J522, O522, H524, J524, O524, H526, J526, O526, H528, J528, O528, H530, J530, O530, H532, J532, O532").Locked = False
            Range("H534, J534, O534, H537, J537, O537, H539, J539, O539, H541, J541, O541, H543, J543, O543, H545, J545, O545, H547, J547, O547, H549, J549, O549, H551, J551, O551, H553, J553, O553, H555, J555, O555, H558, J558, O558, H560, J560, O560, H562, J562, O562, H564, J564, O564, H566, J566, O566, H568, J568, O568, H570, J570, O570, H572, J572, O572, H574, J574, O574, H576, J576, O576, H579, J579, O579, H581, J581, O581, H583, J583, O583, H585, J585, O585, H588, J588, O588, H590, J590, O590, H592, J592, O592, H594, J594, O594, H597, J597, O597, H599, J599, O599, H601, J601, O601, H603, J603, O603, H605, J605, O605, H607, J607, O607, H609, J609, O609, H611, J611, O611, H614, J614, O614, H616, J616, O616, H618, J618, O618, H620, J620, O620, H622, J622, O622, H624, J624, O624, H626, J626, O626, H628, J628, O628, H630, J630, O630, H632, J632").Locked = False
            Range("O632, H635, J635, H651, J651, O651, H654, J654, O654, H656, J656, O656, H658, J658, O658, H660, J660, O660, H663, J663, O663, H665, J665, O665, H667, J667, O667, H669, J669, O669, H671, J671, O671, H674, J674, H690, J690, O690, H693, J693, O693, H697, J697, O697, C699. H699, J699, O699, H701, J701, O701, H704, J704, O704, H706, J706, O706, H708, J708, O708, H710, J710, O710, H712, J712, O712, H714, J714, O714, H716, J716, O716, H719, J719, O719, H721, J721, O721, H723, J723, O723, H725, J725, O725, H727, J727, O727, H729, J729, O729, H731, J731, O731, H733, J733, O733, H736, J736, O736, H738, J738, O738, H740, J740, O740, H742, J742, O742, H744, J744, O744, H746, J746, O746, H748, J748, O748, H750, J750, O750, H752, J752, O752, H754, J754, O754, H757, J757, O757, H759, J759, O759, H761, J761, O761, H763, J763, O763, H766, J766").Locked = False
            Range("O766, H768, J768, O768, H770, J770, O770, H772, J772, O772, H774, J774, O774, H776, J776, O776, H778, J778, O778, H781, J781, O781, H783, J783, O783, H785, J785, O785, H801, J801, O801, H804, J804, O804, C806, C811, J811, O811, C813, J813, O813, C815, J815, O815, C820, J820, O820, H824, J824, O824, H827, J827, O827, H830, J830, O830").Locked = False
        Case "Kostenberechnung +/- 20%"
            Range("H66, J66, O66, C69, F69, J69, O69, C70, F70; H70, J70, O70, C71, F70, J71, O71, H74, J74, O74, H76, J76, O76, H78, J78, O78, H80, J80, O80, H82, J82, O82, H84, J84, O84, H86, J86, O86, H88, J88, O88, H90, J90, O90, H92, J92, O92, H95, J95, O95, H97, J97, O97, H99, J99, O99, H101, J101, O101, H118, J118, O118, H121, J121, O121, H123, J123, O123, H125, J125, O125, H127, J127, O127, H129, J129, O129, H131, J131, O131, H134, J134, O134, H136, J136, O136, H138, J138, O138, H140, J140, O140, H142, J142, O142, H144, J144, O144, H146, J146, O146, H148, J148, O148, H150, J150, O150, H152, J152, O152, H155, J155, O155, H157, J157, O157, H159, J159, O159, H161, J161, O161, H163, J163, O163, H165, J165, O165, H167, J167, O167, H169, J169, O169, H171, J171, O171, H173, J173, O173, H176, J176, O176, H179, J179, O179, H181, J181, O181, H183, J183, O183").Locked = False
            Range("H186, J186, H203, J203, O203, H206, J206, O206, H208, J208, O208, H210, J210, O210, H212, J212, O212, H214, J214, O214, H217, J217, O217, H219, J219, O219, H221, J221, O221, H223, J223, O223, H225, J225, O225, H227, J227, O227, H229, J229, O229, H231, J231, O231, H233, J233, O233, H236, J236, O236, H238, J238, O238, H240, J240, O240, H242, J242, O242, H244, J244, O244, H246, J246, O246, H248, J248, O248, H250, J250, O250, H252, J252, O252, H254, J254, O254, H257, J257, O257, H259, J259, O259, H261, J261, O261, H263, J263, O263, H265, J265, O265, H267, J267, O267, H269, J269, O269, H271, J271, O271, H274, J274, O274, H276, J276, O276, H278, J278, O278, H280, J280, O280, H282, J282, O282, H287, J287, O287, H289, J289, O289, H291, J291, O291, H293, J293, O293, H295, J295, O295, H298, J298, O298, H300, J300, O300, H302, J302, O302").Locked = False
            Range("H304, J304, O304, H307, J307, O307, H309, J309, O309, H311, J311, O311, H313, J313, O313, H315, J315, O315, H317, J317, O317, H319, J319, O319, H321, J321, O321, H323, J323, O323, H325, J325, O325, H328, J328, H344, J344, N344, H347, J347, N347, H349, J349, N349, H351, J351, N351, H353, J353, N353, H355, J355, N355, H358, J358, N358, H360, J360, N360, H362, J362, N362, H364, J364, N364, H366, J366, N366, H369, J369, N369, H371, J371, N371, H373, J373, N373, H375, J375, N375, H377, J377, N377, H379, J379, N379, H382, J382, N382, H384, J384, N384, H386, J386, N386, H388, J388, N388, H390, J390, N390, H392, J392, N392, H394, J394, N394, H396, J396, N396, H399, J399, N399, H401, J401, N401, H403, J403, N403, H405, J405, N405, H407, J407, N407, H409, J409, N409, H411, J411, N411, H413, J413, N413, H415, J415, N415, H418, J418, N418").Locked = False
            Range("H420, J420, N420, H422, J422, N422, H424, J424, N424, H426, J426, N426, H428, J428, N428, H430, J430, N430, H433, J433, N433, H435, J435, N435, H437, J437, N437, H439, J439, N439, H441, J441, N441, H443, J443, N443, H445, J445, N445, H447, J447, N447, H449, J449, N449, H451, J451, N451, H454, J454, N454, H456, J456, N456, H458, J458, N458, H460, J460, N460, H462, J462, N462, H464, J464, N464, H466, J466, N466, H469, J469, N469, H471, J471, N471, H473, J473, N473, H475, J475, N475, H477, J477, N477, H479, J479, N479, H481, J481, N481, H483, J483, N483, H485, J485, N485, H487, J487, N487, H490, J490, H506, J506, O506, H509, J509, O509, H511, J511, O511, H513, J513, O513, H515, J515, O515, H518, J518, O518, H520, J520, O520, H522, J522, O522, H524, J524, O524, H526, J526, O526, H528, J528, O528, H530, J530, O530, H532, J532, O532").Locked = False
            Range("H534, J534, O534, H537, J537, O537, H539, J539, O539, H541, J541, O541, H543, J543, O543, H545, J545, O545, H547, J547, O547, H549, J549, O549, H551, J551, O551, H553, J553, O553, H555, J555, O555, H558, J558, O558, H560, J560, O560, H562, J562, O562, H564, J564, O564, H566, J566, O566, H568, J568, O568, H570, J570, O570, H572, J572, O572, H574, J574, O574, H576, J576, O576, H579, J579, O579, H581, J581, O581, H583, J583, O583, H585, J585, O585, H588, J588, O588, H590, J590, O590, H592, J592, O592, H594, J594, O594, H597, J597, O597, H599, J599, O599, H601, J601, O601, H603, J603, O603, H605, J605, O605, H607, J607, O607, H609, J609, O609, H611, J611, O611, H614, J614, O614, H616, J616, O616, H618, J618, O618, H620, J620, O620, H622, J622, O622, H624, J624, O624, H626, J626, O626, H628, J628, O628, H630, J630, O630, H632, J632").Locked = False
            Range("O632, H635, J635, H651, J651, O651, H654, J654, O654, H656, J656, O656, H658, J658, O658, H660, J660, O660, H663, J663, O663, H665, J665, O665, H667, J667, O667, H669, J669, O669, H671, J671, O671, H674, J674, H690, J690, O690, H693, J693, O693, H697, J697, O697, C699. H699, J699, O699, H701, J701, O701, H704, J704, O704, H706, J706, O706, H708, J708, O708, H710, J710, O710, H712, J712, O712, H714, J714, O714, H716, J716, O716, H719, J719, O719, H721, J721, O721, H723, J723, O723, H725, J725, O725, H727, J727, O727, H729, J729, O729, H731, J731, O731, H733, J733, O733, H736, J736, O736, H738, J738, O738, H740, J740, O740, H742, J742, O742, H744, J744, O744, H746, J746, O746, H748, J748, O748, H750, J750, O750, H752, J752, O752, H754, J754, O754, H757, J757, O757, H759, J759, O759, H761, J761, O761, H763, J763, O763, H766, J766").Locked = False
            Range("O766, H768, J768, O768, H770, J770, O770, H772, J772, O772, H774, J774, O774, H776, J776, O776, H778, J778, O778, H781, J781, O781, H783, J783, O783, H785, J785, O785, H801, J801, O801, H804, J804, O804, C806, C811, J811, O811, C813, J813, O813, C815, J815, O815, C820, J820, O820, H824, J824, O824, H827, J827, O827, H830, J830, O830").Locked = False
        Case "Kostenanschlag +/- 10%"
            Range("H66, J66, O66, C69, F69, J69, O69, C70, F70; H70, J70, O70, C71, F70, J71, O71, H74, J74, O74, H76, J76, O76, H78, J78, O78, H80, J80, O80, H82, J82, O82, H84, J84, O84, H86, J86, O86, H88, J88, O88, H90, J90, O90, H92, J92, O92, H95, J95, O95, H97, J97, O97, H99, J99, O99, H101, J101, O101, H118, J118, O118, H121, J121, O121, H123, J123, O123, H125, J125, O125, H127, J127, O127, H129, J129, O129, H131, J131, O131, H134, J134, O134, H136, J136, O136, H138, J138, O138, H140, J140, O140, H142, J142, O142, H144, J144, O144, H146, J146, O146, H148, J148, O148, H150, J150, O150, H152, J152, O152, H155, J155, O155, H157, J157, O157, H159, J159, O159, H161, J161, O161, H163, J163, O163, H165, J165, O165, H167, J167, O167, H169, J169, O169, H171, J171, O171, H173, J173, O173, H176, J176, O176, H179, J179, O179, H181, J181, O181, H183, J183, O183").Locked = False
            Range("H186, J186, H203, J203, O203, H206, J206, O206, H208, J208, O208, H210, J210, O210, H212, J212, O212, H214, J214, O214, H217, J217, O217, H219, J219, O219, H221, J221, O221, H223, J223, O223, H225, J225, O225, H227, J227, O227, H229, J229, O229, H231, J231, O231, H233, J233, O233, H236, J236, O236, H238, J238, O238, H240, J240, O240, H242, J242, O242, H244, J244, O244, H246, J246, O246, H248, J248, O248, H250, J250, O250, H252, J252, O252, H254, J254, O254, H257, J257, O257, H259, J259, O259, H261, J261, O261, H263, J263, O263, H265, J265, O265, H267, J267, O267, H269, J269, O269, H271, J271, O271, H274, J274, O274, H276, J276, O276, H278, J278, O278, H280, J280, O280, H282, J282, O282, H287, J287, O287, H289, J289, O289, H291, J291, O291, H293, J293, O293, H295, J295, O295, H298, J298, O298, H300, J300, O300, H302, J302, O302").Locked = False
            Range("H304, J304, O304, H307, J307, O307, H309, J309, O309, H311, J311, O311, H313, J313, O313, H315, J315, O315, H317, J317, O317, H319, J319, O319, H321, J321, O321, H323, J323, O323, H325, J325, O325, H328, J328, H344, J344, N344, H347, J347, N347, H349, J349, N349, H351, J351, N351, H353, J353, N353, H355, J355, N355, H358, J358, N358, H360, J360, N360, H362, J362, N362, H364, J364, N364, H366, J366, N366, H369, J369, N369, H371, J371, N371, H373, J373, N373, H375, J375, N375, H377, J377, N377, H379, J379, N379, H382, J382, N382, H384, J384, N384, H386, J386, N386, H388, J388, N388, H390, J390, N390, H392, J392, N392, H394, J394, N394, H396, J396, N396, H399, J399, N399, H401, J401, N401, H403, J403, N403, H405, J405, N405, H407, J407, N407, H409, J409, N409, H411, J411, N411, H413, J413, N413, H415, J415, N415, H418, J418, N418").Locked = False
            Range("H420, J420, N420, H422, J422, N422, H424, J424, N424, H426, J426, N426, H428, J428, N428, H430, J430, N430, H433, J433, N433, H435, J435, N435, H437, J437, N437, H439, J439, N439, H441, J441, N441, H443, J443, N443, H445, J445, N445, H447, J447, N447, H449, J449, N449, H451, J451, N451, H454, J454, N454, H456, J456, N456, H458, J458, N458, H460, J460, N460, H462, J462, N462, H464, J464, N464, H466, J466, N466, H469, J469, N469, H471, J471, N471, H473, J473, N473, H475, J475, N475, H477, J477, N477, H479, J479, N479, H481, J481, N481, H483, J483, N483, H485, J485, N485, H487, J487, N487, H490, J490, H506, J506, O506, H509, J509, O509, H511, J511, O511, H513, J513, O513, H515, J515, O515, H518, J518, O518, H520, J520, O520, H522, J522, O522, H524, J524, O524, H526, J526, O526, H528, J528, O528, H530, J530, O530, H532, J532, O532").Locked = False
            Range("H534, J534, O534, H537, J537, O537, H539, J539, O539, H541, J541, O541, H543, J543, O543, H545, J545, O545, H547, J547, O547, H549, J549, O549, H551, J551, O551, H553, J553, O553, H555, J555, O555, H558, J558, O558, H560, J560, O560, H562, J562, O562, H564, J564, O564, H566, J566, O566, H568, J568, O568, H570, J570, O570, H572, J572, O572, H574, J574, O574, H576, J576, O576, H579, J579, O579, H581, J581, O581, H583, J583, O583, H585, J585, O585, H588, J588, O588, H590, J590, O590, H592, J592, O592, H594, J594, O594, H597, J597, O597, H599, J599, O599, H601, J601, O601, H603, J603, O603, H605, J605, O605, H607, J607, O607, H609, J609, O609, H611, J611, O611, H614, J614, O614, H616, J616, O616, H618, J618, O618, H620, J620, O620, H622, J622, O622, H624, J624, O624, H626, J626, O626, H628, J628, O628, H630, J630, O630, H632, J632").Locked = False
            Range("O632, H635, J635, H651, J651, O651, H654, J654, O654, H656, J656, O656, H658, J658, O658, H660, J660, O660, H663, J663, O663, H665, J665, O665, H667, J667, O667, H669, J669, O669, H671, J671, O671, H674, J674, H690, J690, O690, H693, J693, O693, H697, J697, O697, C699. H699, J699, O699, H701, J701, O701, H704, J704, O704, H706, J706, O706, H708, J708, O708, H710, J710, O710, H712, J712, O712, H714, J714, O714, H716, J716, O716, H719, J719, O719, H721, J721, O721, H723, J723, O723, H725, J725, O725, H727, J727, O727, H729, J729, O729, H731, J731, O731, H733, J733, O733, H736, J736, O736, H738, J738, O738, H740, J740, O740, H742, J742, O742, H744, J744, O744, H746, J746, O746, H748, J748, O748, H750, J750, O750, H752, J752, O752, H754, J754, O754, H757, J757, O757, H759, J759, O759, H761, J761, O761, H763, J763, O763, H766, J766").Locked = False
            Range("O766, H768, J768, O768, H770, J770, O770, H772, J772, O772, H774, J774, O774, H776, J776, O776, H778, J778, O778, H781, J781, O781, H783, J783, O783, H785, J785, O785, H801, J801, O801, H804, J804, O804, C806, C811, J811, O811, C813, J813, O813, C815, J815, O815, C820, J820, O820, H824, J824, O824, H827, J827, O827, H830, J830, O830").Locked = False
        Case Else
            Range("$C$15").Locked = False
    End Select
Ende:
    ActiveSheet.Protect
    Application.EnableEvents = True

End Sub
 

Danke vorab.


Ihre Antwort
  • Bitte beschreiben Sie Ihr Problem möglichst ausführlich. (Wichtige Info z.B.: Office Version, Betriebssystem, Wo genau kommen Sie nicht weiter)
  • Bitte helfen Sie ebenfalls wenn Ihnen geholfen werden konnte und markieren Sie Ihre Anfrage als erledigt (Klick auf Häckchen)
  • Bei Crossposting, entsprechende Links auf andere Forenbeiträge beifügen / nachtragen
  • Codeschnipsel am besten über den Code-Button im Text-Editor einfügen
  • Die Angabe der Emailadresse ist freiwillig und wird nur verwendet, um Sie bei Antworten auf Ihren Beitrag zu benachrichtigen
Thema: Name: Email:

 
 

  • Bitte beschreiben Sie Ihr Problem möglichst ausführlich. (Wichtige Info z.B.: Office Version, Betriebssystem, Wo genau kommen Sie nicht weiter)
  • Bitte helfen Sie ebenfalls wenn Ihnen geholfen werden konnte und markieren Sie Ihre Anfrage als erledigt (Klick auf Häckchen)
  • Bei Crossposting, entsprechende Links auf andere Forenbeiträge beifügen / nachtragen
  • Codeschnipsel am besten über den Code-Button im Text-Editor einfügen
  • Die Angabe der Emailadresse ist freiwillig und wird nur verwendet, um Sie bei Antworten auf Ihren Beitrag zu benachrichtigen

Thema Datum  Von Nutzer Rating
Antwort
Rot Zellen sperren/entsperren
28.07.2022 16:00:49 OnlyLookOutside
Solved
28.07.2022 17:55:37 Gast21544
NotSolved
28.07.2022 18:04:01 xlKing
NotSolved
28.07.2022 18:07:19 xlKing
NotSolved
01.08.2022 16:44:11 OnlyLookOutside
NotSolved
01.08.2022 17:40:29 xlKing
NotSolved