This is a 3 part help
Part 1 & 2
I have adapted a database form to try and fit my 11 column requirements. No luck with 'FIND ALL' and 'AMEND' features.
FIND ALL code: problem is it only shows 4 of the 11 column titles and debugger points to here [.List(.ListCount - 1, 10) = c.Offset(0, 10).Value
]
Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Sheet1.Range("a8", Range("k65536").End(xlUp))
Set rng = Sheet1.Range("a7", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A8").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.ListBox1.Clear
For Each c In rng
With Me.ListBox1
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Offset(0, 1).Value
.List(.ListCount - 1, 2) = c.Offset(0, 2).Value
.List(.ListCount - 1, 3) = c.Offset(0, 3).Value
.List(.ListCount - 1, 4) = c.Offset(0, 4).Value
.List(.ListCount - 1, 5) = c.Offset(0, 5).Value
.List(.ListCount - 1, 6) = c.Offset(0, 6).Value
.List(.ListCount - 1, 7) = c.Offset(0, 7).Value
.List(.ListCount - 1, 8) = c.Offset(0, 8).Value
.List(.ListCount - 1, 9) = c.Offset(0, 9).Value
.List(.ListCount - 1, 10) = c.Offset(0, 10).Value
.List(.ListCount - 1, 11) = c.Offset(0, 11).Value
End With
Next c
End With
End Sub
Private Sub cmbLast_Click()
Dim LastCl As Range
Set LastCl = Range("a65536").End(xlUp) 'last used cell in column A
With Me
.cmbAmend.Enabled = False
.cmbDelete.Enabled = False
.cmbAdd.Enabled = True
.TextBox1.Value = LastCl.Value
.TextBox2.Value = LastCl.Offset(0, 1).Value
.TextBox3.Value = LastCl.Offset(0, 2).Value
.TextBox4.Value = LastCl.Offset(0, 3).Value
.TextBox5.Value = LastCl.Offset(0, 4).Value
.TextBox6.Value = LastCl.Offset(0, 5).Value
.TextBox7.Value = LastCl.Offset(0, 6).Value
.TextBox8.Value = LastCl.Offset(0, 7).Value
.TextBox9.Value = LastCl.Offset(0, 8).Value
.TextBox10.Value = LastCl.Offset(0, 9).Value
.TextBox11.Value = LastCl.Offset(0, 10).Value
End With
End Sub
AMEND code: the error is Sheet1.ShowAllData failed
Private Sub cmbAmend_Click()
Application.ScreenUpdating = False
If rng Is Nothing Then GoTo skip
For Each c In rng
If r = 0 Then c.Select
r = r - 1
Next c
skip:
Set c = ActiveCell
c.Value = Me.TextBox1.Value ' write amendments to database
c.Offset(0, 1).Value = Me.TextBox2.Value
c.Offset(0, 2).Value = Me.TextBox3.Value
c.Offset(0, 3).Value = Me.TextBox4.Value
c.Offset(0, 4).Value = Me.TextBox5.Value
c.Offset(0, 5).Value = Me.TextBox6.Value
c.Offset(0, 6).Value = Me.TextBox7.Value
c.Offset(0, 7).Value = Me.TextBox8.Value
c.Offset(0, 8).Value = Me.TextBox9.Value
c.Offset(0, 9).Value = Me.TextBox10.Value
c.Offset(0, 10).Value = Me.TextBox11.Value
'restore Form
With Me
.cmbAmend.Enabled = False
.cmbDelete.Enabled = False
.cmbAdd.Enabled = True
.TextBox1.Value = vbNullString
.TextBox2.Value = vbNullString
.TextBox3.Value = vbNullString
.TextBox4.Value = vbNullString
.TextBox5.Value = vbNullString
.TextBox6.Value = vbNullString
.TextBox7.Value = vbNullString
.TextBox8.Value = vbNullString
.TextBox9.Value = vbNullString
.TextBox10.Value = vbNullString
.TextBox11.Value = vbNullString
.Height = frmHt
End With
If Sheet1.AutoFilterMode Then Sheet1.ShowAllData
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
Part 3
Do I have the begining of the page code correct for my 11 column database?
Option Explicit
Dim MyArray(11, 11)
Public MyData As Range, c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range
Part 1 & 2
I have adapted a database form to try and fit my 11 column requirements. No luck with 'FIND ALL' and 'AMEND' features.
FIND ALL code: problem is it only shows 4 of the 11 column titles and debugger points to here [.List(.ListCount - 1, 10) = c.Offset(0, 10).Value
]
Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Sheet1.Range("a8", Range("k65536").End(xlUp))
Set rng = Sheet1.Range("a7", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A8").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.ListBox1.Clear
For Each c In rng
With Me.ListBox1
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Offset(0, 1).Value
.List(.ListCount - 1, 2) = c.Offset(0, 2).Value
.List(.ListCount - 1, 3) = c.Offset(0, 3).Value
.List(.ListCount - 1, 4) = c.Offset(0, 4).Value
.List(.ListCount - 1, 5) = c.Offset(0, 5).Value
.List(.ListCount - 1, 6) = c.Offset(0, 6).Value
.List(.ListCount - 1, 7) = c.Offset(0, 7).Value
.List(.ListCount - 1, 8) = c.Offset(0, 8).Value
.List(.ListCount - 1, 9) = c.Offset(0, 9).Value
.List(.ListCount - 1, 10) = c.Offset(0, 10).Value
.List(.ListCount - 1, 11) = c.Offset(0, 11).Value
End With
Next c
End With
End Sub
Private Sub cmbLast_Click()
Dim LastCl As Range
Set LastCl = Range("a65536").End(xlUp) 'last used cell in column A
With Me
.cmbAmend.Enabled = False
.cmbDelete.Enabled = False
.cmbAdd.Enabled = True
.TextBox1.Value = LastCl.Value
.TextBox2.Value = LastCl.Offset(0, 1).Value
.TextBox3.Value = LastCl.Offset(0, 2).Value
.TextBox4.Value = LastCl.Offset(0, 3).Value
.TextBox5.Value = LastCl.Offset(0, 4).Value
.TextBox6.Value = LastCl.Offset(0, 5).Value
.TextBox7.Value = LastCl.Offset(0, 6).Value
.TextBox8.Value = LastCl.Offset(0, 7).Value
.TextBox9.Value = LastCl.Offset(0, 8).Value
.TextBox10.Value = LastCl.Offset(0, 9).Value
.TextBox11.Value = LastCl.Offset(0, 10).Value
End With
End Sub
AMEND code: the error is Sheet1.ShowAllData failed
Private Sub cmbAmend_Click()
Application.ScreenUpdating = False
If rng Is Nothing Then GoTo skip
For Each c In rng
If r = 0 Then c.Select
r = r - 1
Next c
skip:
Set c = ActiveCell
c.Value = Me.TextBox1.Value ' write amendments to database
c.Offset(0, 1).Value = Me.TextBox2.Value
c.Offset(0, 2).Value = Me.TextBox3.Value
c.Offset(0, 3).Value = Me.TextBox4.Value
c.Offset(0, 4).Value = Me.TextBox5.Value
c.Offset(0, 5).Value = Me.TextBox6.Value
c.Offset(0, 6).Value = Me.TextBox7.Value
c.Offset(0, 7).Value = Me.TextBox8.Value
c.Offset(0, 8).Value = Me.TextBox9.Value
c.Offset(0, 9).Value = Me.TextBox10.Value
c.Offset(0, 10).Value = Me.TextBox11.Value
'restore Form
With Me
.cmbAmend.Enabled = False
.cmbDelete.Enabled = False
.cmbAdd.Enabled = True
.TextBox1.Value = vbNullString
.TextBox2.Value = vbNullString
.TextBox3.Value = vbNullString
.TextBox4.Value = vbNullString
.TextBox5.Value = vbNullString
.TextBox6.Value = vbNullString
.TextBox7.Value = vbNullString
.TextBox8.Value = vbNullString
.TextBox9.Value = vbNullString
.TextBox10.Value = vbNullString
.TextBox11.Value = vbNullString
.Height = frmHt
End With
If Sheet1.AutoFilterMode Then Sheet1.ShowAllData
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
Part 3
Do I have the begining of the page code correct for my 11 column database?
Option Explicit
Dim MyArray(11, 11)
Public MyData As Range, c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range