[VBA] 스핀단추와 유효성검사

오늘은 이것을 해보려고 한다.

 

대안 번호가 1번부터 30번까지 있는데 유효성 검사의 리스트는 0이 아닌 값 까지만 노출이 되어야 하며,  스핀단추의 최소값은 대안번호 1번이며, 그값보다 작아지면 안되며, 반대로 대안번호의 리스트의 최고값보다 값이 커지면 안된다.

 

이런 조건식의 값들은 이벤트로 처리하면 실시간적으로 원하는 구성이 가능하다.

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim rngX As Range: Set rngX = [b2]
    Dim rngAll As Range, rngA As Range
    Dim V(100), Vall
    Dim i&
    Dim V_split
    
    If Intersect(Target, [b2]) Is Nothing Then Exit Sub
    
    With Sheets("데이터")
        Set rngAll = .Range(.[b2], .[b2].End(4))
    End With
    
    For Each rngA In rngAll
    
        If rngA.Next <> 0 Then
        
            V(i) = rngA
            i = i + 1
        
        End If
    
    Next rngA
    
  
    V_split = Application.Evaluate("ROW(1:" & i & ")")
    Vall = Application.Index(V, 0, Application.Transpose(V_split))
    
   
    
    Vall = Join(Vall, ",")                              '= V 배열에 콤마로 연결하여 V에 담아라
    
        With rngX.Validation                            '= 유효성 검사
            .Delete                                     '= 기존 유효성 검사를 삭제하고
            .Add Type:=xlValidateList, Formula1:=Vall   '= 배열 V에 담은 목록을 유효성 검사로 해라
            
        End With
                                                
    
    MsgBox "대안 넘버가 업데이트 되었습니다."
    
    Cancel = True
End Sub

대안번호를 더블클릭하면 대안번호중 0이 아닌값들만 표기가 되는 코드이다.

그건 그렇고 8월말인데 비가 너무 온다...와...떠내려 가는거 아냐???

스핀 단추의 속성은 최소값 1만 세팅해주면 되고 현재값은 자동으로 업데이트 된다. 셀연결은 편한곳 아무곳이나 세팅하면 된다. 단, 최종적으로는 글자색을 하얀색으로 해서 안보이게 해주면 된다.

 

스핀단추는 재밌는게 이렇게 매크로를 지정해서 넣을 수 있다.

매크로 지정에 스핀단추를 연결시킨다.

Sub 스핀단추()

    Dim rngX As Range
    
     
    With Sheets("데이터")
       
        Set rngX = .Range(.[b2], .[b2].End(4)).Find(Sheets("결과").[a2])  '= 데이터 시트에서 대안번호의 위치를 찾는다
       

        If rngX(1, 2) <> 0 Then                                           '= 대안번호의 바로 옆 값이 0이 아니면
        
           [b2] = [a2]                                                    '= 대안번호 출력셀에 스핀단추의 값을 넣어라
    
        Else                                                              '= 대안번호의 바로 옆 값이 0이면 / 유효성 검사 리스트의 최대값을 넘기면
                                                                          '= 유효성검사가 30개중 최대값이 23이면 대안번호는 절대 23을 넘길 수 없다.
           [a1] = [a1] - 1                                                '= 따라서 스핀단추는 +1씩 올라가기 때문에 -1을 함으로서 최대값 23을 넘길수 없게 한다.
           
        End If
    End With

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)                       '= 이 구문을 넣지 않으면 유효성검사와 스핀단추와 연결이 되지 않아
    If Intersect(Target, [b2]) Is Nothing Then Exit Sub                   '= 값들이 서로 따로 움직이게 된다.
    [a1] = Replace([b2], "대안-", "")                                     '= 대안번호가 변동이 되면 그 값을 스핀단추 연결셀에 업데이트 시키라는 구문이다.
End Sub

어떻게보면 약간 트릭이 있는 코드이다.

 

그래도 그 효과는 무시할 수 없으니, 쓰윽 들여다보길 바란다.

스핀단추(23.08.29).xlsm

0.03MB