it shown the runtime error on the
call personaldata.show(vbmodeless)
when i click the command button on sheet1(data)
i tried to do this excel vba references to wrox excel 2003 vba chapter9
some how it did not works
any one can help?
following is the code
or you can email me for the files if need to know more bout the problems

Option Explicit

Private Sub CommandButton9_Click()
  Call PersonalData.Show(vbModeless)
  End Sub

#If False Then
  Dim RangeData As Range
  Dim Data As Variant

  Set RangeData = Range("Database").Rows(2)
  Data = RangeData.Value

  With PersonalData
    .TXTENUMBER = Data(1, 1)
    .DEPART = Data(1, 2)
    .TXTFAMILYN = Data(1, 3)
    .TXTFIRSTN = Data(1, 4)
    .MARITALS = Data(1, 7)
    .TXTNI = Data(1, 9)
    .VISAS = Data(1, 10)
    .TextBox2 = Data(1, 13)
    .TextBox1 = Data(1, 14)
    .TXTHOME = Data(1, 16)
    .TXTMOBILE = Data(1, 17)
    .TXTNAME = Data(1, 18)
    .TXTNUM = Data(1, 19)
    .TXTBANKNAME = Data(1, 31)
    .TXTACNAME = Data(1, 32)
    .TXTSORT = Data(1, 33)
    .TXTACCOUNTN = Data(1, 34)
    .TXTROLL = Data(1, 35)


  Select Case Data(1, 8)
  Case "FEMALE"
    .OptionButtonFemale.Value = True
  Case "MALE"
    .OptionButtonMale = True
  End Select


    .Show
  If (Not .Cancelled) Then
    Data(1, 1) = .TXTENUMBER
    Data(1, 2) = .DEPART
    Data(1, 3) = .TXTFAMILYN
    Data(1, 4) = .TXTFIRSTN
    Data(1, 7) = .MARITALS
    Data(1, 9) = .TXTNI
    Data(1, 10) = .VISAS
    Data(1, 13) = .TextBox2
    Data(1, 14) = .TextBox1
    Data(1, 16) = .TXTHOME
    Data(1, 17) = .TXTMOBILE
    Data(1, 18) = .TXTHOME
    Data(1, 19) = .TXTNUM
    Data(1, 31) = .TXTBANKNAME
    Data(1, 32) = .TXTACNAME
    Data(1, 33) = .TXTSORT
    Data(1, 34) = .TXTACCOUNTN
    Data(1, 35) = .TXTROLL


    Select Case True
      Case .OptionButtonFemale.Value
        Data(1, 8) = "FEMALE"
      Case .OptionButtonMale.Value
        Data(1, 8) = "MALE"
    End Select

    RangeData.Value = Data
  End If
  End With

  Call Unload(PersonalData)
#End If

Private Sub Worksheet_Change(ByVal TARGET As Range)

  If (PersonalData.Visible) Then
    MsgBox "Changed"
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal TARGET As Range)

End Sub



bringing accounting and computing knowledge to kitchen

Hi CBChiam

Your reference to the named range "Database" does this named range exist in the workbook? As this looks like this is the most likely reason for the code to fail.

Cheers

Matt

yes, you are right
the name database does not exist on the work book, how can i fix it? actually i tried to make it work with the data at 2nd roll. it got around 42 items, but i just need some of them to be shown in userform.
can help? or you need the files?
[email protected]

bringing accounting and computing knowledge to kitchen

Is the range called Database dynamic, i.e. changing in quantity of records?

Presuming it is, the best way I would suggest using this is to use something like the following...
    Dim myRange As String
    Dim RangeData As Range
    Dim Data As Variant

    myRange = Range("A1:AH" & Range("A65000").End(xlUp).Row).Address
    Set RangeData = Range(myRange).Rows(2)

You are then defining the value of myRange as a string and using it in the following line to select the changing data range rather than using a named area called "Database".
To use this, column 1 must have a value on every line, then replace the 'AH' in "A1:AH" to the last column of your known data range. I have assumed that as the data(1,35) array is defining the right most column of the database and hence established it is column AH.

Hope this helps.


Matt

thanks very much
the one fix
but have new problems

If (PersonalData.Visible) Then

the above sentences was high light (yellow)
error 1004, how to debug this one?
thanks again.

bringing accounting and computing knowledge to kitchen

The code is alright, the problem is probably that the code is in the wrong code window.

Cut the code out and place it in the code window behind the sheet object Sheet "Data". or put another way, open the Project Explorer in the VBEditor, Double click the object displayed as Sheet1(Data). This will open a blank window. Paste the code in the blank window.

This then acknowledges that when the sheet that the code resides in is changed then the test is evaluated.

Cheers

Matt

Option Explicit

Private Sub CommandButton9_Click()
  Call PersonalData.Show(vbModeless)
  End Sub
    Dim myRange As String
    Dim RangeData As Range
    Dim Data As Variant

    myRange = Range("A1:AH" & Range("A65000").End(xlUp).Row).Address
    Set RangeData = Range(myRange).Rows(2)


#If False Then
  Dim RangeData As Range
  Dim Data As Variant

  Set RangeData = Range("Database").Rows(2)
  Data = RangeData.Value

  With PersonalData
    .TXTENUMBER = Data(1, 1)
    .DEPART = Data(1, 2)
    .TXTFAMILYN = Data(1, 3)
    .TXTFIRSTN = Data(1, 4)
    .MARITALS = Data(1, 7)
    .TXTNI = Data(1, 9)
    .VISAS = Data(1, 10)
    .TextBox2 = Data(1, 13)
    .TextBox1 = Data(1, 14)
    .TXTHOME = Data(1, 16)
    .TXTMOBILE = Data(1, 17)
    .TXTNAME = Data(1, 18)
    .TXTNUM = Data(1, 19)
    .TXTBANKNAME = Data(1, 31)
    .TXTACNAME = Data(1, 32)
    .TXTSORT = Data(1, 33)
    .TXTACCOUNTN = Data(1, 34)
    .TXTROLL = Data(1, 35)


  Select Case Data(1, 8)
  Case "FEMALE"
    .OptionButtonFemale.Value = True
  Case "MALE"
    .OptionButtonMale = True
  End Select


    .Show
  If (Not .Cancelled) Then
    Data(1, 1) = .TXTENUMBER
    Data(1, 2) = .DEPART
    Data(1, 3) = .TXTFAMILYN
    Data(1, 4) = .TXTFIRSTN
    Data(1, 7) = .MARITALS
    Data(1, 9) = .TXTNI
    Data(1, 10) = .VISAS
    Data(1, 13) = .TextBox2
    Data(1, 14) = .TextBox1
    Data(1, 16) = .TXTHOME
    Data(1, 17) = .TXTMOBILE
    Data(1, 18) = .TXTHOME
    Data(1, 19) = .TXTNUM
    Data(1, 31) = .TXTBANKNAME
    Data(1, 32) = .TXTACNAME
    Data(1, 33) = .TXTSORT
    Data(1, 34) = .TXTACCOUNTN
    Data(1, 35) = .TXTROLL


    Select Case True
      Case .OptionButtonFemale.Value
        Data(1, 8) = "FEMALE"
      Case .OptionButtonMale.Value
        Data(1, 8) = "MALE"
    End Select

    RangeData.Value = Data
  End If
  End With

  Call Unload(PersonalData)
#End If

Private Sub Worksheet_Change(ByVal TARGET As Range)

  If (PersonalData.Visible) Then
    MsgBox "Changed"
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal TARGET As Range)

End Sub

all the code is in the sheet1(data)
thanks for help, but i doesnt work out yet

bringing accounting and computing knowledge to kitchen

Correspondence by email...

Cheers

Matt