エクセル側の起動時の処理は
エクセルが非表示の場合ユーザーフォームを起動とし
Private Sub Workbook_Open()
If Application.Visible = False Then
UserForm1.Show vbModeless
End If
End Sub
ユーザーフォーム起動時にエクセルの設定シートから設定を読み込み
ユーザーフォーム上に設置したコントロールの表示切り替えを実施しています。
'【ユーザーフォーム起動】
Private Sub UserForm_Initialize()
Dim i As Long
Dim St As Worksheet
Dim udtJwDataConvert As JwDataConvert
Set St = ThisWorkbook.Sheets("設定")
On Error GoTo ErrHndl
'Pageセレクト
MultiPage1.Value = St.Cells(1, 1).Value
'取得データ
For i = 1 To 10
Controls("Data" & i).Value = St.Cells(i, 2).Value
Next
'置換
For i = 1 To 5
Controls("Conv" & i).Value = St.Cells(i, 3).Value
Next
For i = 30 To 39
Controls("RepCc" & i - 30).Value = St.Cells(i, 3).Value
Next
'全角半角変換
For i = 1 To 8
Controls("CheckBox" & i).Value = St.Cells(i, 4).Value
Next
For i = 30 To 39
Controls("StrHanCc" & i - 30).Value = St.Cells(i, 4).Value
Next
For i = 40 To 49
Controls("StrZenCc" & i - 40).Value = St.Cells(i, 4).Value
Next
'大小変換
For i = 1 To 3
Controls("OptionButton" & i).Value = St.Cells(i, 5).Value
Next
For i = 30 To 39
Controls("StrDaiSyouCc" & i - 30).Value = St.Cells(i, 5).Value
Next
'追加
For i = 1 To 18
Controls("Add" & i).Value = St.Cells(i, 6).Value
Next
For i = 30 To 39
Controls("AddCc" & i - 30).Value = St.Cells(i, 6).Value
Next
'削除
For i = 1 To 18
Controls("Del" & i).Value = St.Cells(i, 7).Value
Next
For i = 30 To 39
Controls("DelCc" & i - 30).Value = St.Cells(i, 7).Value
Next
'書式変換
For i = 1 To 1
Controls("FormConv" & i).Value = St.Cells(i, 8).Value
Next
For i = 40 To 49
Controls("FormConvCc" & i - 40).Value = St.Cells(i, 8).Value
Next
'コントロールの表示
Call ConvEnabled
Call ZenHanEnabled
Call AddEnabled
Call DelEnabled
Exit Sub
ErrHndl:
MsgBox "データ読込失敗しました。"
End Sub
Private Sub ConvEnabled()
If Conv1.Value = "" Then
CommandButton2.Enabled = False
CommandButton15.Enabled = False
Else
CommandButton2.Enabled = True
CommandButton15.Enabled = True
End If
End Sub
Private Sub ZenHanEnabled()
Dim i As Byte, Flg As Boolean
For i = 1 To 4
If Controls("CheckBox" & i).Value = True Then
Flg = True
End If
Next
If Flg = False Then
CommandButton4.Enabled = False
Else
CommandButton4.Enabled = True
End If
End Sub
Private Sub AddEnabled()
Dim i As Byte
'データ変換
For i = 1 To 6
If Controls("Add" & i).Value = True Then
Controls("Add" & i * 2 + 5).Enabled = True
Controls("Add" & i * 2 + 6).Enabled = True
Else
Controls("Add" & i * 2 + 5).Enabled = False
Controls("Add" & i * 2 + 6).Enabled = False
End If
Next
End Sub
Private Sub DelEnabled()
Dim i As Byte
'データ変換
For i = 1 To 6
If Controls("Del" & i).Value = True Then
Controls("Del" & i * 2 + 5).Enabled = True
Controls("Del" & i * 2 + 6).Enabled = True
Else
Controls("Del" & i * 2 + 5).Enabled = False
Controls("Del" & i * 2 + 6).Enabled = False
End If
Next
End Sub