Using If Statement VBA to Submit to Additional Worksheet

[daviv]

Hello!

I have searched this topic out quite a bit today and so far I have only been able to find options where you add a button to a form to sort data into different sheets. What I am trying to accomplish (and I am sorry I am very new to this) if when a user submits a form, one of the iRow answers determines if it is written to an additional sheet. So say I have 3 sheets, one is just the full listing (which would be hidden), one is for if option xyz (via option button) the data is written to worksheet a. Else if option abc is selected the data is written to worksheet b. I am trying to have a full sheet with all the information and then two sheets as sort of a filter/breakdown by the specific option. I am trying to do this under the submit portion of the module and I can't seem to formulate the if statement to work with this.

Sub Submit()
    Dim sh As Worksheet
    Dim iRow As Long
   
    Set sh = ThisWorkbook.Sheets("Database")
   
    iRow = [Counta(Database!A:A)] + 1
   
    With sh
   
        .Cells(iRow, 1) = frmform.txtName.Value
        .Cells(iRow, 2) = frmform.txtEmail.Value
        .Cells(iRow, 3) = IIf(frmform.optVPN.Value = True, "VPN", "Travel")
        .Cells(iRow, 4) = frmform.txtDate.Value
        .Cells(iRow, 5) = frmform.txtIP.Value
        .Cells(iRow, 6) = frmform.txtLocation.Value
        .Cells(iRow, 7) = Application.UserName
        .Cells(iRow, 8) = [Text(Now(), "MM-DD-YYYY HH:MM")]
           
       
    End With


End Sub

I am trying to specify if optVPN.Value = True to then write to the worksheet VPN else it writes to a worksheet TRAVEL. Let me know if that makes sense or if it is doable. I have seen it where someone adds a button to like the master sheet to then break out the selected criteria into specific sheets but I have not been able to get that to work. That code above is just the base code, there is no format I am using that works because I am just too noobish I guess Thanks!


EDIT: Sorry, Database is the full listing of all the form entries and then there are two sheets, VPN and TRAVEL that filter that. I realized I defined just the one sheet which is the catchall sheet. Thanks!

 

[Micron]

Maybe your issue is that Iff means "Immediate If" and it's an Access function (you posted this in Excel so I don't know if that's relevant) - it's not a vba statement at all. For that, use "If". I'll show you how you can condense your code a bit and (hopefully) it would still run. Will take a few minutes.

 

[Micron]

OK, I guess I'm wrong - it's a vba function. Unfortunately, just saying nothing works doesn't shed much light on your issue. What happens when you run that?

 

[daviv]

I haven't been able to to formulate the if so that was sort of my baseline question is embedding the if in there with the appropriate call to then write to the sheet. I attempted to use

 .Cells(iRow, 3) = IIf(frmform.optVPN.Value = True, "VPN", "Travel")

and directly below it define that .Value=True then

  Set sh = ThisWorkbook.Sheets("Database")

but my inexperience shows up with how to format the "if" portion.

 

[Micron]

Well if you can't answer questions, I guess I can't help you. Obviously I don't have an optVPN that I can use but I can substitute other T/F logic.
This puts VPN in the cell.
.Cells(iRow, 3) = IIf(6 = 6 = True, "VPN", "Travel")