Table manipulation in Excel VBA

[dhosi439]

I have a table with 19 columns. Column A contains image name data, column B contains building name data. The other 17 columns are used as columns of hard drives, we have 17 hard drives to track data on.

For each image name in Column A an X in placed into the columns of every hard drive containing the image. I gather data via a user form, which asks for the date created, image name, building name, and the drive it is on. The final box asking for the drive name is used to input an X, this is selected using a case statement, however what I would like to do is have 17 check boxes or if there is something simpler, to be able to select multiple drives, instead of one drive at a time, to input the data into the columns, as of now I can only input one column and then need to repeat the image name again to have it show in another column from the user form. This will cause too much repetitive data. Is it possible to do something like this instead of a select statement? I'm not sure how to have the data be interpreted to apply an X to multiple columns.

Also would it be possible to have a user form or method to edit data already in the table, for instance if I have an image name with X's in 5 of the 17 columns, would it be possible check the table for the image name, if it exists remove the row and add then add the data using the current or new method for entering data to the table from the user form?

Any assistance would be great.

Here is the code for my user form:

Option Explicit

Private Sub UserForm_Activate()
'sets source on Activation
frmMain.cbImage.RowSource = "Image"
frmMain.cbBuilding.RowSource = "Building"

End Sub

'Used to add date to userform

Private Sub cmdDate_Click()
'initialize calendar
    OpenCalendar
    Me.cbImage.SetFocus
End Sub

'Used to input data to spreadsheet

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet

'Set worksheet based on Venue Row Source
Set ws = Worksheets("DriveData")


'find  first empty row in database
If cbxFirst.Value = "True" Then
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Row
Else
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
End If

'check for a date
If Trim(Me.txtDate.Value) = "" Then
  Me.cmdDate.SetFocus
  MsgBox "Please enter a date"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.cbImage.Value & "_" & Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.cbBuilding.Value

Select Case Me.cbDrive.Value

          Case 1

              ws.Cells(iRow, 3).Value = "X"

          Case 2

              ws.Cells(iRow, 4).Value = "X"

          Case 3

              ws.Cells(iRow, 5).Value = "X"

          Case 4

              ws.Cells(iRow, 6).Value = "X"

          Case 5

              ws.Cells(iRow, 7).Value = "X"
              
          Case 6

              ws.Cells(iRow, 8).Value = "X"
              
          Case 7

              ws.Cells(iRow, 9).Value = "X"

          Case 8

              ws.Cells(iRow, 10).Value = "X"
              
          Case 9

              ws.Cells(iRow, 11).Value = "X"
              
          Case 10

              ws.Cells(iRow, 12).Value = "X"
              
          Case 11

              ws.Cells(iRow, 13).Value = "X"
                       
          Case 12

              ws.Cells(iRow, 14).Value = "X"
          
          Case 13
          
              ws.Cells(iRow, 15).Value = "X"
                                         
          Case 14

              ws.Cells(iRow, 16).Value = "X"
                                                  
          Case 15

              ws.Cells(iRow, 17).Value = "X"
                                                           
          Case 16

              ws.Cells(iRow, 18).Value = "X"
                                                                   
           Case "BU"

              ws.Cells(iRow, 19).Value = "X"

    End Select


'clear the data
Me.txtDate.Value = ""
Me.cbImage.Value = ""
Me.cbBuilding.Value = ""
Me.cbDrive.Value = ""


'Set focus to close button
Me.cmdClose.SetFocus

'Workbook Refresh
ActiveWorkbook.RefreshAll

'Refresh Venues to see new data entries
frmMain.cbImage.RowSource = "Image"
frmMain.cbBuilding.RowSource = "Building"


End Sub

'Button to clear data from user form
Private Sub cmdClear_Click()
'Clears userform fields
Me.txtDate.Value = ""
Me.cbImage.Value = ""
Me.cbBuilding.Value = ""
Me.cbDrive.Value = ""
Me.cmdDate.SetFocus
End Sub

Private Sub cmdClose_Click()
  Unload Me
End Sub