REALLY SIMPLE QUESTION- Option buttons and sheet selection

[nac3371]

I'm really rusty on my VBA and this is really simple, but I can't seem to get it to work. I'm trying to create a database in excel. Basically I have a data entry form and one part allows you 4 options. The sheet that the data is entered in depends on which option you choose. So if you choose the first, the data enters into sheet 1 when you press submit. I just can't get this to work for some reason.

 

[bertie]

Hi and welcome to the forum.

Here is how I would approach coding your Submit button.

I have assumed your from is on a worksheet, so we need to set this up as the source, e.g.,

   [COLOR=green]'set up the source worksheet[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsSource = Sheets("[COLOR=red]Form[/COLOR]")

Then we have to determine which Option Button was selected and assign a worksheet name accordingly:

   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]True[/COLOR]
      [COLOR=darkblue]Case[/COLOR] OptionButton1
         sheetName = "Sheet1"
      [COLOR=darkblue]Case[/COLOR] OptionButton2
         sheetName = "Sheet2"
      [COLOR=darkblue]Case[/COLOR] OptionButton3
         sheetName = "Sheet3"
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
         sheetName = "Sheet4"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]

And we update this worksheet with the values on the "Form":

   [COLOR=green]'get the next available row in column A[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets(sheetName)
      rw = .Range("A" & .Rows.Count).End(xlUp).Row
 
      [COLOR=green]'sample output[/COLOR]
      .Range("A" & rw).Value = wsSource.Range("A1").Value
      .Range("B" & rw).Value = wsSource.Range("B1").Value
      [COLOR=green]'etc[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

The ranges in the sample code will need to be edited to meet your needs but I hope this gives you enough ideas to ehlp solve your problem.

Here is the full code. Make a copy of your worksheet before playing about with it.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmd[COLOR=darkblue]Sub[/COLOR]mit_Click()
   [COLOR=darkblue]Dim[/COLOR] wsSource [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] sheetName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=green]'set up the source worksheet[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsSource = Sheets("Form")
 
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]True[/COLOR]
      [COLOR=darkblue]Case[/COLOR] OptionButton1
         sheetName = "Sheet1"
      [COLOR=darkblue]Case[/COLOR] OptionButton2
         sheetName = "Sheet2"
      [COLOR=darkblue]Case[/COLOR] OptionButton3
         sheetName = "Sheet3"
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
         sheetName = "Sheet4"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
 
   [COLOR=green]'get the next available row in column A[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets(sheetName)
      rw = .Range("A" & .Rows.Count).End(xlUp).Row
 
      [COLOR=green]'sample output[/COLOR]
      .Range("A" & rw).Value = wsSource.Range("A1").Value
      .Range("B" & rw).Value = wsSource.Range("B1").Value
      [COLOR=green]'etc[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'free up memory[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsSource = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] Sub