Weather Forecast Spreadsheet User Form Help

[Inti]

Hello all,

I have been working on this spreadsheet for a couple of weeks now. I am completely new to the language and wish to develop my skills. Along with the help of this forum and others I've been able to get to the code that is attached. To this point there are two issues.

The first being that I can't get the delete button to delete the row that corresponds to the item selected in the listbox of the delete tab in the userform. Right now it's deleting everything in the spreadsheet. Second, whenever I try to edit a site in the edit tab I keep getting the error msg created by me which is there to prevent people from leaving a field. empty.

The way that it is supposed to work is the following. I click on the Update Sites button and the userform opens. If I wish to delete an entry I select it in the listbox and then the whole row in the excel spreadsheet "Site List" is deleted, also the entry is removed from the listbox. If the I wish to edit an entry, that being, the name, latitude or longitude, I go into the edit tab, select a site from the combobox and then edit the information as needed. If a field is left empty you get a message saying that you need to fill everything, if the lat or lon are non-numeric you get a message saying so.

Could someone help with these two issues? Any input is appreciated! Thank you in advance!

[Bob Phillips]

The first problem does not occur for me, it just deletes the one row.

The second problem is because your code is looking at the Add tab textboxes, not the edit tab textboxes. BTW, I would always qualify an object with the property you are after, such as TextBoxEditSiteName.Text, and never rely on the defualt, it will catch you one day.

BBTW, you can remove an item from a listbox directly, not reloading, but yoiu have to work backwards. And you can load a lisbox directly, get the range into a variant and set listbox.list = variable.

[Inti]

Thank you for pointing out that the code was calling to the add tab and not edit, that worked!

I figured out why the delete error is not reproducing for you. If I try to delete any item before the last item in the list it deletes the corresponding row. But if you try to delete the last item it deletes everything. Also on the edit tab im getting an error 1004 for this part of the code

Any suggestions to fix the edit and delete tab? Thank you in advance for the help!



Worksheets("Site List").Cells(selRow, 1) = sitelink1
Worksheets("Site List").Cells(selRow, 2) = sitename1
Worksheets("Site List").Cells(selRow, 3) = lat1
Worksheets("Site List").Cells(selRow, 4) = lon1



Which comes form here.



Dim selRow As Long
Dim lat1 As String
Dim lon1 As String
Dim sitelink1 As String
Dim sitename1 As String
selRow = Worksheets("Site List").Cells(ComboBoxEditSiteName.ListIndex + 1, 4)
lat1 = Format(cpUserForm.TextBoxAddLat.Value, "0.0000000000000000")
lon1 = Format(cpUserForm.TextBoxAddLong.Value, "0.0000000000000000")
sitelink1 = "api.yr.no/weatherapi/locationforecastlts/1.2/?lat=" & lat1 & ";lon=" & lon1
sitename1 = cpUserForm.TextBoxEditSiteName.Value
Worksheets("Site List").Cells(selRow, 1) = sitelink1
Worksheets("Site List").Cells(selRow, 2) = sitename1
Worksheets("Site List").Cells(selRow, 3) = lat1
Worksheets("Site List").Cells(selRow, 4) = lon1

[Bob Phillips]

The code below fixes the delete problem, but I cannot see that code you mention for edit in the workbook


Private Sub CommandButtonDeleteUpdate_Click()
''''''Edited''''''''
'use RemoveItem method to delete a ListBox row. The below code deletes the row from the
'ListBox and also deletes the row items (or rows) in the worksheet
Dim n As Long
Dim k As Long
Dim var As Variant


'deleting row from ListBox using RemoveItem method:

Sheet2.Activate
'check all items in a ListBox; reverse order (Step -1) is used because rows are being deleted from ListBox.
With ListBoxDeletePickSiteList

For n = .ListCount - 1 To 0 Step -1

If .Selected(n) = True Then

'item to be deleted is stored in the variable named var
var = .List(n, 0)
.RemoveItem (n)

'determine row number in which items are to be deleted; Note: value of variable named var
'is derived from first column, hence Range("A:A") is searched in the Match formula.
k = Application.Match(var, Worksheets("Site List").Range("B:B"), 0)

'delete the row to the corresponding item in listbox
Worksheets("Site List").Rows(k).Delete

Exit For
End If
Next n

.ListIndex = -1
End With
End Sub