Populating cells with Cascading Combobox Info: Not populating

[j21chris]

I realized I should have put Userform Combobox into the thread title. Sorry!

I'm new to VBA but not new to coding or to excel. I have used multiple google searches and youtube videos (You're welcome Alphabet) to build vba code that will copy a template worksheet, name the worksheet using an input box, and populate one cell in the new worksheet also using the inputbox method. I was also able to create a Userform containing a cascading combobox that works great...that is until I try to add the Userform data to cells in the newly created (copied from template) worksheet. I have searched high and low and I cannot, for the life of me, figure out why it is not working. I feel confident it is my green VBA skills that is making this difficult for me to troubleshoot.

Could someone help me tackle this primary challenge? Again for clarity, I simply need to get my Userform (combobox & textbox) data from the Userfrom into various cells in the newly created worksheet, hopefully using the variable containing the new worksheet name. I have much work to do beyond that, but I'm trying to just eat this bite of the elephant.

Here is my Userform code:


Private Sub cmbLandUse_Cat_Change()


Me.cmbLandUse_Type = ""


Select Case Me.cmbLandUse_Cat
Case "Agricultural"
Me.cmbLandUse_Type.RowSource = "Agricultural"
Case "Business"
Me.cmbLandUse_Type.RowSource = "Business"
Case "Industrial"
Me.cmbLandUse_Type.RowSource = "Industrial"
Case "Lawn"
Me.cmbLandUse_Type.RowSource = "Lawn"
Case "Pasture"
Me.cmbLandUse_Type.RowSource = "Pasture"
Case "Residential"
Me.cmbLandUse_Type.RowSource = "Residential"
Case "Streets"
Me.cmbLandUse_Type.RowSource = "Streets"
Case "Woodland"
Me.cmbLandUse_Type.RowSource = "Woodland"
Case Else
'do nothing
End Select
End Sub


Private Sub cbAdd_Category_Click()
Dim ssheet As Worksheet


Set ssheet = ActiveSheet.Name


'place selection into cell
ssheet.Cells(1, 4).Value = Me.cmbLandUse_Cat
ssheet.Cells(2, 4).Value = Me.cmbLandUse_Type
ssheet.Cells(3, 4).Value = CInt(Me.txbxLand_Use_Area)

End Sub


Private Sub cbCancel_Click()


Unload Me


End Sub



I suspect the issue may be in where/how I am calling the userform and that someone helping to solve this would also need my primary module code so here is that as well:

Sub Create_New_Drng_Tab()


Application.ScreenUpdating = False


Dim newName As String
newName = Application.InputBox("Name of Drainage Area", "Drainage Area Name", Type:=2)
Dim OK_zone As Integer




' Drainage Area Name Input Dialog Box, if name repeated, exits sub. If cancel, exit sub
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(newName) Then
MsgBox "This drainage area name already exists."
Exit Sub
End If

If newName = "False" Then Exit Sub: Rem cancel pressed

Next

' Zone Input Dialog Box, if escape exit sub
OK_zone = Application.InputBox(vbNewLine & "Geographical Zone Number 1-5" _
& vbNewLine & vbNewLine & _
"See Figure 1-13, ODOT DDM Page 1-29", "Oklahoma Zone Identification", Type:=1)

Sheets("Template").Visible = True
Sheets("Template").Copy Before:=Sheets("Template")
ActiveSheet.Name = newName
'Sheets("Template").Visible = xlVeryHidden

' Insert Zone into OK Geographical Zone Cell
Range("M12").Value = OK_zone

usfrmLand_Use_Category.Show

Application.ScreenUpdating = True

End Sub

Thanks in advance!!

JD

 

[sijpie]

Hi JD, welcome here. Please if you post code, put the code between code tags (see my signature line in red) or use MrExcelHTML or similar (as I did here). That makes the code easier to read.

I think your problem is where you set the worksheet to the activesheet NAME instead of to the active sheet object. I have gone through your code and made some other light changes to make it more legible and easier to write.


<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmbLandUse_Cat_Change()<br><br>    Me.cmbLandUse_Type = ""<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Me.cmbLandUse_Type<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Me.cmbLandUse_Cat<br>            <SPAN style="color:#00007F">Case</SPAN> "Agricultural"<br>                .RowSource = "Agricultural"<br>            <SPAN style="color:#00007F">Case</SPAN> "Business"<br>                .RowSource = "Business"<br>            <SPAN style="color:#00007F">Case</SPAN> "Industrial"<br>                .RowSource = "Industrial"<br>            <SPAN style="color:#00007F">Case</SPAN> "Lawn"<br>                .RowSource = "Lawn"<br>            <SPAN style="color:#00007F">Case</SPAN> "Pasture"<br>                .RowSource = "Pasture"<br>            <SPAN style="color:#00007F">Case</SPAN> "Residential"<br>                .RowSource = "Residential"<br>            <SPAN style="color:#00007F">Case</SPAN> "Streets"<br>                .RowSource = "Streets"<br>            <SPAN style="color:#00007F">Case</SPAN> "Woodland"<br>                .RowSource = "Woodland"<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                <SPAN style="color:#007F00">'do nothing</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cbAdd_Category_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsSheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsSheet = ActiveSheet       <SPAN style="color:#007F00">' >> not: Set wsSheet = ActiveSheet.Name</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> wsSheet<br>        <SPAN style="color:#007F00">'place selection into cell</SPAN><br>        .Cells(1, 4).Value = Me.cmbLandUse_Cat<br>        .Cells(2, 4).Value = Me.cmbLandUse_Type<br>        .Cells(3, 4).Value = <SPAN style="color:#00007F">CInt</SPAN>(Me.txbxLand_Use_Area)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsSheet = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cbCancel_Click()<br><br>    Unload Me<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br><SPAN style="color:#007F00">'I suspect the issue may be in where/how I am calling the userform and that someone helping to solve this would also need my primary module code so here is that as well:</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Create_New_Drng_Tab()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> newName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> OK_zone <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsWS <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <br>    newName = Application.InputBox("Name of Drainage Area", "Drainage Area Name", Type:=2)<br>    <SPAN style="color:#00007F">If</SPAN> newName = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> Sub: Rem cancel pressed<br>    <br>    <SPAN style="color:#007F00">' Drainage Area Name Input Dialog Box, if name repeated, exits sub. If cancel, exit sub</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsWS <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        <SPAN style="color:#00007F">If</SPAN> LCase(wsWS.Name) = LCase(newName) <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "This drainage area name already exists."<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wsWS<br>    <br>    <SPAN style="color:#007F00">' Zone Input Dialog Box, if escape exit sub</SPAN><br>    OK_zone = Application.InputBox(vbNewLine & "Geographical Zone Number 1-5" _<br>            & vbNewLine & vbNewLine & _<br>            "See Figure 1-13, ODOT DDM Page 1-29", _<br>            "Oklahoma Zone Identification", Type:=1)<br>    <SPAN style="color:#00007F">If</SPAN> OK_zone = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> Sub: Rem cancel pressed<br>    <br>    Sheets("Template").Visible = <SPAN style="color:#00007F">True</SPAN><br>    Sheets("Template").Copy Before:=Sheets("Template")<br>    ActiveSheet.Name = newName<br>    Sheets("Template").Visible = xlVeryHidden<br>    <br>    <SPAN style="color:#007F00">' Insert Zone into OK Geographical Zone Cell</SPAN><br>    Range("M12").Value = OK_zone<br>    <br>    usfrmLand_Use_Category.Show<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> wsWS = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>