Unhide and Activate a Worksheet using Radio Button on UserFrom

[DaveGib]

Hi.....
I have been battling for hours trying to solve this and I hope someone can please help me

I have a workbook with some hidden worksheets, that open depending on the users choice in a Userform.

The userform is opened when the user clicks a button on the "HOME" Tab, he/she is then presented with two radio buttons, to force a selection of one of two sheets.

Upon selecting the appropriate sheet is activated, unhidden, and the Home tab hidden.

This all seems to work ok, i.e. the sheet required becomes unhidden, and the HOME sheet gets hidden, but when I try and capture information on the sheet displayed, the information that is captured actually appears on the HOME sheet, and NOT the sheet displayed!!

The two option buttons are called obCapture and obPrint

the code below is what I have..........

 
Private Sub CBOk_Click()
    Application.ScreenUpdating = False

          '***** CHECK THAT A SELECTION IS MADE
    
    If Me.obCapture.Value = False And Me.obPrint.Value = False Then
        MsgBox "Please Select Print Form OR Capture!!"
    End If
    
         '***** IF PRINT OPTION IS SELECTED, OPEN PRINT FORM
    
    If Me.obPrint.Value = True Then
        Worksheets("Materials Form").Activate
            ActiveSheet.Visible = True
            Worksheets("Home").Visible = False
        Unload Me
    End If

          '***** IF CAPTURE IS SELECTED, OPEN CAPTURE FORM

    If Me.obCapture.Value = True Then
        Worksheets("Materials Capture").Activate
            ActiveSheet.Visible = True
            Worksheets("Home").Visible = False
        Unload Me
    End If
    
    Application.ScreenUpdating = True
End Sub

[p45cal]

but when I try and capture information on the sheet displayed, the information that is captured actually appears on the HOME sheet, and NOT the sheet displayed!!

So.. what code are you using to try and capture information, and where is this capturing code; the userform's code module, a sheet's code module or a standard code module?

[YasserKhalil]

when I try and capture information on the sheet displayed, the information that is captured actually appears on the HOME sheet, and NOT the sheet displayed!!

What do you mean with capture information : you mean copy specific range on the activesheet or using Printscreen button to take a snapshot ..
I find both of them working fine on the active sheet
I can't recognize any problem.. Please more clarification

[SamT]

when I try and capture information on the sheet displayed, the  information that is captured actually appears on the HOME sheet, and NOT  the sheet displayed!

I am sorry, but that statement is semantically invalid in VBA.

Note sequence of events below.

Private Sub CBOk_Click()
  Application.ScreenUpdating = False

'***** CHECK THAT A SELECTION IS MADE
  If Me.obCapture.Value = False And Me.obPrint.Value = False Then
    MsgBox "Please Select Print Form OR Capture!!"
  End If
  
'***** IF PRINT OPTION IS SELECTED, OPEN PRINT FORM
  If Me.obPrint.Value = True Then
    With Worksheets("Materials Form")
      .Visible = True
      .Activate
    End With
    Worksheets("Home").Visible = False
    Unload Me
  End If

'***** IF CAPTURE IS SELECTED, OPEN CAPTURE FORM
  If Me.obCapture.Value = True Then
    With Worksheets("Materials Capture")
      .Visible = True
      .Activate
    End With
    Worksheets("Home").Visible = False
    Unload Me
  End If
  
  Application.ScreenUpdating = True
End Sub

[DaveGib]

Hi P45cal,
Thanks for replying.
On the "Home" Tab I have a series of buttons, which open various sheets, each button opens a specific sheet, and I don't have any problems with them, - but the one button opens a userform which has two options, to either open a sheet that has an input form on it, or the other to capture the data from the input form.
The code above is in the userform code module.
I could create two separate buttons to do what I want, but as they are both related to each other I thought it would be cool to go this approach ( If I can!!)

[DaveGib]

Hi SamT and Yasserkhalil,
SamT - thank you for taking the trouble to improve my code.
I know this sounds weird, and I can't understand it, but when I run the above code (or mine) the 'Materials Form' or 'Materials Capture' - depending, opens and is displayed on screen as it should, but when I try to capture data, whatever I enter in any cell of the 'Materials Form' disappears as soon as I press enter or move to another cell, but when I go to the 'Home' sheet the information that I entered in the 'Materials Form' appears in the same cell number, but on the 'Home' sheet!!! (I know you probably think I am smoking my socks or something - but it's true!!)
However if I unhide the Home sheet and physically open it, (by clicking on it) and without doing anything else, immediately open the 'Materials Form' again - I can then capture information normally- the information captured stays on the sheet (as it should) and doesn't disappear on pressing enter, or appear on the Home sheet!
The strange thing is that this happens to both the sheets that I try to unhide and open from the userform.
I can only come to the conclusion that these two sheets have somehow become corrupted.
I am going to recreate the two sheets, and if that doesn't work, I will create two separate buttons to open the two sheets!!
Thanks once again to you both!!

[SamT]

Capture is not a word! (not in VBA)

What are you means by these characters "Capture"????

Theses are South Afrkaans languagesd no? Canadian, Yes?

Seriously Dave, the use of local slang can be very confusing on the other side of our (really small) world.

One thing I can definitely say, the problem is not in your UserForm's button code.

but when I try to capture data, whatever I enter in any cell of the 'Materials Form' disappears as soon as I press enter or move to another cell, but when I go to the 'Home' sheet the information that I entered in the 'Materials Form' appears in the same cell number, but on the 'Home' sheet!!

"Capture" implies (in VBA) that some code is reading data from somewhere (WorkSheets("Materials Form")) and writing data somewhere else (WorkSheets("Materials Capture").) That the data you enter goes somewhere else (Worksheets("Home")) is proof that data is being read and written, (Captured.) The Events you mentioned, (Pressing Enter, selecting another Cell) tells me that your problem is in the Reading and Writing code, (Capture Code.).

Look for any Subs (Macros) the have the words "_Calculate" or "_Change" (With the underscore) in their names. I can't tell you for sure where the code will be. That depends on the exact Sub Name. Any Sub name with an underscore in it is suspect.

[DaveGib]

Hi SamT,
Thanks for your comments and tips!!...
Apologies if I confused you, - here in South Africa a person who enters data/information into a program on a computer is known as a 'Data Capturer' hence my using the word 'capture' - I will remember for any future posts!
I will have a look to see if there is perhaps any code as you suggest that could be causing the problem, failing which I will have to use two buttons to select the two sheets separately.
Thanks very much for your time - much appreciated!
Dave

[SamT]

Data Capture = Data Entry

Capturer = Entry Clerk. I know which I would rather be.