Weather Forecast Spreadsheet User Form Help

Hello all! I have added a user form to this spreadsheet to make it a little more user friendly to edit/add/delete some information. Now the API used only needs longitude/latitude (lat/lon) to be input by the user. In this case it would be good that for each lat/lon to have a custom name added by the user.

Could someone help me to link the userform, that's already made, to the information in the spreadsheet?

I was thinking it would be convenient to have the "Site Name" added to a column in the sheet named "Site List" and then have the "Fore Cast Data" pull the names from there.

Any help is appreciated! I am learning VBA by fire and this has proven to be pretty interesting.

Originally Posted by 6StringJazzer

Where does the information from the userform go? How does this all work? Walk us through the operational concept.

Good idea, thank you! Allright so the way I see it whenever the user needs to just update the forecast for the existing sites (that's what I would call the lat/lon location) they would just hit the refresh button on the spreadsheet. If for some reason they would need to add more sites then they would click the update button. That's where the userform "control panel" comes in. At this point they will have multiple options, which I divided in different tabs for organization.

The first option, "Add", here they would input the Site Name, which is a custom string the user will use to identify a specific Lat/Cord. Also, in this option they would have to input the Lat/Lon in decimal value. If it's not in decimal value and the site name is not input the person should not be able to go forward, otherwise the query to the API would fail or in the case no "name" is given then the data will be poorly identified. At this point the user will either choose to add changes with the "update" button, clear the information or close the window with the "clear" and "cancel" button respectively. When putting the Site name and Lat/Lon, the Lat/Lon should be added to the API link which for example is this.

http://api.yr.no/weatherapi/locationforecastlts/1.2/?lat=37.621343;lon=-122.378644

The "Site Name" I think would be best to be added next to the site link which is currently found in the "Site List" sheet. This "Site Name" located in the Site List will then be also found on the "Forecast Data" sheet to identify each forecast to the correct location.

For the second option "delete" is self explanatory, on the list box the "site names" will appear in a scrollable box, the user will select one or more of the site names and once the user clicks update the links to the API will get deleted from the "Site List" sheet and therefore be eliminated from the "Forecast Data" sheet once refreshed.

For the third option, "Edit", the user will use the drop down box to select one site to edit and then according to what the person updates, whether it's the "Site Name", Lat, Lon or all that's what would be edited from the "Site List" sheet. Again, once the person hits "Update" the items will be edited and refreshed.

I think it would also be good to add a error catcher. If the user added an invalid lat/lon then the API would fail, the user would see an error message explaining that the lat/lon is invalid when refreshed or as soon as it's edited/added into the workbook.

If there is a simpler way to do this please let me know, this is just the way, I as a user would like to see things working.

Thank you all for your help once again!

Where do you want the site name to go? It doesn't look like you currently have site names in your data.

Also, to completely code your form would be a bit more work than I am able to do currently. I can give you some pointers. I've set up a framework for code in your form module.

I was thinking, maybe the site name could go right next to the cell where the link to the API is located. For example if the first API link is in A1 then the Site Name could go in B1.

On that note I know that every time the program runs it will reset the "Site List" site to 1 column.

With Sheets("site list")
  Set SiteList = .Range("a1").Resize(.Cells(Rows.Count, "A").End(xlUp).Row, 1)
End With

Is there a reason for this? I mean, does having another value in another cell conflict with the rest of the code?

6StringJazzer, thank you very much for your help. I understand that it's a lot of work so whatever input is greatly appreciated. Definitely gets me started.

Originally Posted by Inti

On that note I know that every time the program runs it will reset the "Site List" site to 1 column.
Is there a reason for this?

Yes, a very explicit reason. The "1" in your code says, "Resize this range to be 1 column wide."

With Sheets("site list")
  Set SiteList = .Range("a1").Resize(.Cells(Rows.Count, "A").End(xlUp).Row, 1)
End With

I mean, does having another value in another cell conflict with the rest of the code?

I don't understand this question. You mention "another value" and "another cell" but I don't know what is the original value and the original cell.

I don't understand this question. You mention "another value" and "another cell" but I don't know what is the original value and the original cell.

Oh yes, I understand what is causing it to be resized. I did read the code carefully. I am wondering why is it being resized. At one point I eliminated that part of the code so it wouldn't be resized and manually put the names of the sites. At which point the code gave me an error. So I am wondering if it's being resized for a legitimate reason.

So the original values on the sheet cells are the API links on row A but we would need for the site name to be the "another value" in row B.

I am a little confused. Did you not write the code? You are describing it as though it's someone else's code. It is being resized so that the range SiteList contains the exact number of sites in the list. The way I prefer to do that is define a dynamic range in the spreadsheet rather than calculating it in the code, but either way is valid.

Adding data in column B shouldn't have any impact at all on the code.

I have been building it with the help of people on forums. At the same time I am learning and modifying it as I go. I am going to give it another go at adding a column B. See what that produces.

Allright I got it done, learned a couple of things on the way there. Now I'll move to the userform.

All right so I worked on this for a couple of hours! Like I said, I am just starting so I've been reading a lot about this. In generalI think I got everything but I am not being able to run the Control Panel userform. I won't get an error it's just freezing. Can you please verify this? Thank you in advance for your help!

By the way, the things that I added have a small tag on top of them like this...

'''''''Edited'''''''
''''''''''''''''''''

You've done a great job. I love it when people take initiative. You do have a number of small errors that are tripping you up. Here is a revision that fixes them:

Weather Forecast Spreadsheet Draft V7.xlsm

It's not freezing. It's just got a really, really big job to do. This is causing a problem:

   For Each C In Worksheets("Site List").Range("B:B") 'edited range

You are looping through every cell in column B. That is over a million cells. I updated the code to just loop through the cells that are being used.

This causes a compile error, the correct collection is Worksheets. I updated the code.

   ''''''Edited''''''
    With Me
        .TextBoxEditSiteName.Value = Format(Worksheet("Site List").Cells(.ComboBoxEditSiteName.ListIndex, 2), "0%")
        .TextBoxEditLong.Value = Format(Worksheet("Site List").Cells(.ComboBoxEditSiteName.ListIndex, 4), "0%")
        .TextBoxEditLat.Value = Format(Worksheet("Site List").Cells(.ComboBoxEditSiteName.ListIndex, 3), "0%")
    End With
   ''''''''''''''''''

In CommandButtonEditUpdate_Click, the variable emptyRow was not declared or set. I fixed that.

Your code to add a row to Site List need to refer to Cells, not cell. I fixed it.

        Worksheets("Site List").cell(emptyRow, 2) = cpUserForm.TextBoxAddSiteName.Value
        Worksheets("Site List").cell(emptyRow, 3) = cpUserForm.TextBoxAddLat.Value
        Worksheets("Site List").cell(emptyRow, 4) = cpUserForm.TextBoxAddLong.Value

Great! Thank you so much 6StringJazzer it's definitely running now! There are some small bugs but i am tackling them now. Most of them look like they are just a matter of the way that I coded my parts. I'll keep you posted! Thank you once again!!

So here is an update to the progress on today. There are a couple of bugs and I managed to solve some of them. There are others where I am stumped but will continue to work on them.

[Fixed] Add feature is adding the sites one on top of another.
Changed the counter “emptyRow” to count using the second column rather than the first column. The counter was doing one before becausecol A gets populated with the info of col B-D.

[Fixed] Edit is populating the data for the item before the selected item in the listbox
I learned that the listIndex is a number starting at 0, therefore added + 1 to the counter

[Pending] The delete feature is not working right. I completely forgot to add that to the code! I found a snippet on google and modified it to our needs but runtime error 13 appears. Type mismatch

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

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

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

        If ListBoxDeletePickSiteList.Selected(n) = True Then

            'item to be deleted is stored in the variable named var
            var = ListBoxDeletePickSiteList.List(n, 0)
            ListBoxDeletePickSiteList.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

        End If

    Next n
    ''''''''''''''''''''

    ' Refresh list
    LoadListBoxDeletePickSiteList

End Sub

[Pending] When trying to edit the name/lat/lon on "Edit Tab" I get the error message with all three errors, that I must include a sitename, lat and long even though the whole list is populated. Consequently, the information is not being edited.

Again any help is greatly appreciated! So close to finishing this, but this has definitely helped me learn a lot!