|
基本的にりんさんのレスと同じですが、ユーザーフォームを表示するところから
の設定を絡めるなら
Private MyCaption As String
Private Sub UserForm_Initialize()
MyCaption = "文字見易く"
Me.CommandButton1.Caption = MyCaption
End Sub
Private Sub CommandButton1_Click()
Dim i As Long, k As Long
Dim mySource As Worksheet
Dim myRange As Range, c As Range
Dim s1 As String
Dim s2 As String
Dim j As Long
Dim v As Variant
CommandButton1.Caption = "作動中" Then
ActiveSheet.Unprotect
Application.ScreenUpdating = False
v = Array("C3", "G3", "N3", "S3", "V3")
j = 8
For i = LBound(v) To UBound(v)
Worksheets("支払入力").Range(v(i)).Resize(20).Formula _
= "=LEFTB(発注先!$B" & j & ",6)" & _
" & "" "" & 発注先!$J" & j & ""
j = j + 20
Next
Range("C28,C3:D22,G3:J22,N3:O22,S3:S22,V3:W22") _
.Font.ColorIndex = 10
Range("C3").Select
Set mySource = Worksheets("発注先")
Set myRange = Range("C3:C22,G3:G22,N3:N22,S3:S22,V3:V22")
k = 0
For Each c In myRange
With c
k = k + 1
s1 = mySource.Cells(7 + k, 2).Value
s2 = mySource.Cells(7 + k, 10).Value
.Value = StrConv(LeftB(StrConv(s1, vbFromUnicode), 6), vbUnicode) & " " & s2
i = InStr(.Text, " ")
If i Then
.Characters(i).Font.Color = vbBlue
End If
End With
Next
With Application
.EnableEvents = False
.ScreenUpdating = True
End With
Call 保護8
CommandButton1.Caption = MyCaption
ActiveSheet.Protect
End Sub
コマンドボタンのイベントマクロは、冒頭部分と終わりのあたりのみ
「推測を入れた」変更をしています。中間部分は弄っていません。
|
|