各位大大好：
有一MULTIPAGE表單，其名稱為
員工基本資料。並配有comBobox6
其用途為供使用者可選取員工姓名之用。
現小弟之問題為在新增員工時，選取“新增”按鈕
新增員工；並同時設有直接變更定義名稱
的參照範圍。之後選取comBobox6發現其內的參照範圍
並未改變；其結果也就無法取得已新增的
員工姓名。請問comBobox6應如何修改才
可成功取得實際參照範圍呢？
其語法如下：
Private Sub ComboBox6_Change()
Dim mAr1 As Variant
Dim mStr1$, mStr1a$
Dim mSht1 As Worksheet
Dim mRng1 As Range, mRng1a As Range
Set mSht1 = Worksheets("員工基本資料")
mSht1.Select
With mSht1
Set mRng1 = Range("員工基本資料")
With mRng1
mAr1 = mRng1.Offset(1, 1).Resize(.Rows.Count - 1, 1).Value
mAr1 = WorksheetFunction.Transpose(mAr1)
With ComboBox6
.Width = .Width * 1
.ColumnCount = 1
.List = mAr1
.Value = .List(.ListIndex)
mStr1 = .Value
'.SetFocus
End With
End With
Set mRng1a = mRng1.Columns(2).Find(what:=mStr1, lookat:=xlWhole, searchorder:=xlByRows)
If Not mRng1a Is Nothing Then
'MsgBox mRng1a.Value
TextBox1.Value = mRng1a.Offset(, -1).Value
TextBox2.Value = mRng1a.Value
TextBox3.Value = mRng1a.Offset(, 1).Value
TextBox4.Value = mRng1a.Offset(, 2).Value
TextBox5.Value = mRng1a.Offset(, 3).Value
TextBox6.Value = mRng1a.Offset(, 4).Value
TextBox7.Value = mRng1a.Offset(, 5).Value
TextBox8.Value = mRng1a.Offset(, 7).Value
mStr1a = mRng1a.Offset(, 6).Value
If mStr1a = "在職" Then
OptionButton1.Value = True
ElseIf mStr1a = "離職" Then
OptionButton2.Value = True
End If
mRng1a.Offset(, -1).Select
End If
End With
Set mSht1 = Nothing
Set mRng1 = Nothing
Set mRng1a = Nothing
End Sub
感恩大大！ |
發表於 2010-10-17 22:23
| 
