Stellaaaa
01-29-2019, 08:40 PM
Hi so I've been doing this macro for 2 months already it's almost done. It's my first time to code so I'm really new.
The only problem left is that I don't know why the UserForm doesn't allow me to Update a selected row from the ListBox? It only Updates the 'Environment' column.
I don't get errors but it's not working.
Private Sub btnDelete_Click()
Dim a As Integer
If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Yes") = vbYes Then
For a = 1 To Range("A100000").End(xlUp).Row
If Cells(a, 1) = listHeader.List(listHeader.ListIndex) Then
Rows(a).Select
Selection.Delete
End If
Next a
End If
End Sub
Private Sub btnView_Click()
listHeader.RowSource = "A4:H200"
End Sub
Private Sub cmbAdd_Click()
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Sheets("PRESTAGE DB")
nextrow = sheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
If Me.cmbSchema = "" Or _
Me.cmbEnvironment = "" Or _
Me.cmbHost = "" Or _
Me.cmbIP = "" Or _
Me.cmbAccessible = "" Or _
Me.cmbLast = "" Then
MsgBox "Some fields cannot be blank!", vbCritical, "Data Missing"
Else
sheet.Cells(nextrow, 1) = Me.cmbSchema
sheet.Cells(nextrow, 2) = Me.cmbEnvironment
sheet.Cells(nextrow, 3) = Me.cmbHost
sheet.Cells(nextrow, 4) = Me.cmbIP
sheet.Cells(nextrow, 5) = Me.cmbAccessible
sheet.Cells(nextrow, 6) = Me.cmbLast
sheet.Cells(nextrow, 7) = Me.cmbConfirmation
sheet.Cells(nextrow, 8) = Me.cmbProjects
MsgBox "Data Added!"
End If
End Sub
Private Sub cmbClearFields_Click()
cmbSchema.Text = ""
cmbEnvironment.Text = ""
cmbHost.Text = ""
cmbIP.Text = ""
cmbAccessible.Text = ""
cmbLast.Text = ""
cmbConfirmation.Text = ""
cmbProjects.Text = ""
cmbSearch.Text = ""
End Sub
Private Sub cmbSearch_Change()
'x = ThisWorkbook.Worksheets("PRESTAGE DB") 'JAN 30
x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSearch.Value Then
cmbSchema.Text = Sheets("PRESTAGE DB").Cells(y, 1)
cmbEnvironment.Text = Sheets("PRESTAGE DB").Cells(y, 2)
cmbHost.Text = Sheets("PRESTAGE DB").Cells(y, 3)
cmbIP.Text = Sheets("PRESTAGE DB").Cells(y, 4)
cmbAccessible.Text = Sheets("PRESTAGE DB").Cells(y, 5)
cmbLast.Text = Sheets("PRESTAGE DB").Cells(y, 6)
cmbConfirmation.Text = Sheets("PRESTAGE DB").Cells(y, 7)
cmbProjects.Text = Sheets("PRESTAGE DB").Cells(y, 8)
UserForm1.listHeader.RowSource = "A" + CStr(y) + ": H" + CStr(y)
Exit For
End If
Next y
End Sub
Private Sub cmbUpdate_Click()
Dim x As Long
Dim y As Long
x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSchema.Value Then
Sheets("PRESTAGE DB").Cells(y, 2) = cmbEnvironment.Value
Sheets("PRESTAGE DB").Cells(y, 3) = cmbHost.Value
Sheets("PRESTAGE DB").Cells(y, 4) = cmbIP.Value
Sheets("PRESTAGE DB").Cells(y, 5) = cmbAccessible.Value
Sheets("PRESTAGE DB").Cells(y, 6) = cmbLast.Value
Sheets("PRESTAGE DB").Cells(y, 7) = cmbConfirmation.Value
Sheets("PRESTAGE DB").Cells(y, 8) = cmbProjects.Value
Exit For
End If
Next y
End Sub
Private Sub CommandButton5_Click()
listHeader.RowSource = ""
End Sub
Private Sub listHeader_Click()
cmbSchema.Value = UserForm1.listHeader.Column(0)
cmbEnvironment.Value = UserForm1.listHeader.Column(1)
cmbHost.Value = UserForm1.listHeader.Column(2)
cmbIP.Value = UserForm1.listHeader.Column(3)
cmbAccessible.Value = UserForm1.listHeader.Column(4)
cmbLast.Value = UserForm1.listHeader.Column(5)
cmbConfirmation.Value = UserForm1.listHeader.Column(6)
cmbProjects.Value = UserForm1.listHeader.Column(7)
End Sub
Private Sub UserForm_Initialize()
cmbSearch.List = Sheets("PRESTAGE DB").Range("A4:A10000").Value
cmbEnvironment.AddItem "DEV"
cmbEnvironment.AddItem "UAT"
cmbEnvironment.AddItem "SIT"
cmbEnvironment.AddItem "QA"
cmbEnvironment.AddItem "PROD"
cmbAccessible.AddItem "Y"
cmbAccessible.AddItem "N"
cmbIP.AddItem "1521"
cmbProjects.AddItem "DP - proposed for DEV/SIT"
cmbProjects.AddItem "PH EFUSE SIT"
cmbProjects.AddItem "MyAXA SG DEV/DIT"
End Sub
The only problem left is that I don't know why the UserForm doesn't allow me to Update a selected row from the ListBox? It only Updates the 'Environment' column.
I don't get errors but it's not working.
Private Sub btnDelete_Click()
Dim a As Integer
If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Yes") = vbYes Then
For a = 1 To Range("A100000").End(xlUp).Row
If Cells(a, 1) = listHeader.List(listHeader.ListIndex) Then
Rows(a).Select
Selection.Delete
End If
Next a
End If
End Sub
Private Sub btnView_Click()
listHeader.RowSource = "A4:H200"
End Sub
Private Sub cmbAdd_Click()
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Sheets("PRESTAGE DB")
nextrow = sheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
If Me.cmbSchema = "" Or _
Me.cmbEnvironment = "" Or _
Me.cmbHost = "" Or _
Me.cmbIP = "" Or _
Me.cmbAccessible = "" Or _
Me.cmbLast = "" Then
MsgBox "Some fields cannot be blank!", vbCritical, "Data Missing"
Else
sheet.Cells(nextrow, 1) = Me.cmbSchema
sheet.Cells(nextrow, 2) = Me.cmbEnvironment
sheet.Cells(nextrow, 3) = Me.cmbHost
sheet.Cells(nextrow, 4) = Me.cmbIP
sheet.Cells(nextrow, 5) = Me.cmbAccessible
sheet.Cells(nextrow, 6) = Me.cmbLast
sheet.Cells(nextrow, 7) = Me.cmbConfirmation
sheet.Cells(nextrow, 8) = Me.cmbProjects
MsgBox "Data Added!"
End If
End Sub
Private Sub cmbClearFields_Click()
cmbSchema.Text = ""
cmbEnvironment.Text = ""
cmbHost.Text = ""
cmbIP.Text = ""
cmbAccessible.Text = ""
cmbLast.Text = ""
cmbConfirmation.Text = ""
cmbProjects.Text = ""
cmbSearch.Text = ""
End Sub
Private Sub cmbSearch_Change()
'x = ThisWorkbook.Worksheets("PRESTAGE DB") 'JAN 30
x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSearch.Value Then
cmbSchema.Text = Sheets("PRESTAGE DB").Cells(y, 1)
cmbEnvironment.Text = Sheets("PRESTAGE DB").Cells(y, 2)
cmbHost.Text = Sheets("PRESTAGE DB").Cells(y, 3)
cmbIP.Text = Sheets("PRESTAGE DB").Cells(y, 4)
cmbAccessible.Text = Sheets("PRESTAGE DB").Cells(y, 5)
cmbLast.Text = Sheets("PRESTAGE DB").Cells(y, 6)
cmbConfirmation.Text = Sheets("PRESTAGE DB").Cells(y, 7)
cmbProjects.Text = Sheets("PRESTAGE DB").Cells(y, 8)
UserForm1.listHeader.RowSource = "A" + CStr(y) + ": H" + CStr(y)
Exit For
End If
Next y
End Sub
Private Sub cmbUpdate_Click()
Dim x As Long
Dim y As Long
x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSchema.Value Then
Sheets("PRESTAGE DB").Cells(y, 2) = cmbEnvironment.Value
Sheets("PRESTAGE DB").Cells(y, 3) = cmbHost.Value
Sheets("PRESTAGE DB").Cells(y, 4) = cmbIP.Value
Sheets("PRESTAGE DB").Cells(y, 5) = cmbAccessible.Value
Sheets("PRESTAGE DB").Cells(y, 6) = cmbLast.Value
Sheets("PRESTAGE DB").Cells(y, 7) = cmbConfirmation.Value
Sheets("PRESTAGE DB").Cells(y, 8) = cmbProjects.Value
Exit For
End If
Next y
End Sub
Private Sub CommandButton5_Click()
listHeader.RowSource = ""
End Sub
Private Sub listHeader_Click()
cmbSchema.Value = UserForm1.listHeader.Column(0)
cmbEnvironment.Value = UserForm1.listHeader.Column(1)
cmbHost.Value = UserForm1.listHeader.Column(2)
cmbIP.Value = UserForm1.listHeader.Column(3)
cmbAccessible.Value = UserForm1.listHeader.Column(4)
cmbLast.Value = UserForm1.listHeader.Column(5)
cmbConfirmation.Value = UserForm1.listHeader.Column(6)
cmbProjects.Value = UserForm1.listHeader.Column(7)
End Sub
Private Sub UserForm_Initialize()
cmbSearch.List = Sheets("PRESTAGE DB").Range("A4:A10000").Value
cmbEnvironment.AddItem "DEV"
cmbEnvironment.AddItem "UAT"
cmbEnvironment.AddItem "SIT"
cmbEnvironment.AddItem "QA"
cmbEnvironment.AddItem "PROD"
cmbAccessible.AddItem "Y"
cmbAccessible.AddItem "N"
cmbIP.AddItem "1521"
cmbProjects.AddItem "DP - proposed for DEV/SIT"
cmbProjects.AddItem "PH EFUSE SIT"
cmbProjects.AddItem "MyAXA SG DEV/DIT"
End Sub