Focus cells

**sivdin** · 05-17-2011, 10:06 AM

Hi Friends,

Below code is for an userform. Data gets filled in Sheet1.But Sheet1 is in "Veryhidden mode" for my requirement. So, the range A4 in sheet1 is focussed according to below code.There is Sheet2 which is directly linked with Sheet1. (only Sheet2 is visible inscreen)
All I need is ,The range A4 should be focussed in sheet2. and data should always go into sheet1 in very hidden mode.

Private Sub cmdSUBMIT_CLICK()
ActiveWorkbook.Sheets("Sheet1").Activate
    If cboName.Value = "MICHEL" Then
    Range("A4").Show
    Range("A4").Select
    ElseIf cboName.Value = "ANDY" Then
    Range("A101").Show
    Range("A101").Select
End if

Hope the explanation is clear. Any help please. Thanks in advance

**Domski** · 05-17-2011, 10:15 AM

Hi,

You can't activate or select cells on a very hidden worksheet. In fact in general when writing VBA code you should avoid doing so in the vast majority of cases. What do you want to do with A4/A101?

Dom

**111StepsAhead** · 05-17-2011, 10:20 AM

Where do you assign the data at?
Focus in on sheet2 but put the data on sheet1. (If you are allowed to do this. I think you can.)
You said they are linked, with formulas I guess, so putting data in sheet2 should update sheet1.

**sivdin** · 05-17-2011, 10:33 AM

Hi, I need the selection of cells in visible sheet only.(sheet2)

**sivdin** · 05-17-2011, 10:35 AM

Data is in Sheet1.Sheet2 is linked by formulas(=)
I need cells to focus in Sheet2.
Sheet1 always stays invisible.
According to posted code, the cell focus will be in sheet1 only.

**111StepsAhead** · 05-17-2011, 10:43 AM

So instead of activating sheet1 activate sheet2?

Example code.

Sub cmd_Submit_Click()
'Focus Sheet1.
Worksheets("Sheet1").Activate

'Now focus Sheet2.
Worksheets("Sheet2").Activate

'If check box names entered value is equal to DATA_1
    If cboName.Value = "DATA_1" Then 'do the following
    'Range("A4").Show
    'Select Range A4.
    Range("A4").Select
    'Else if check box names value is equal to DATA_2
    ElseIf cboName.Value = "DATA_2" Then 'do the following
    'Range("A101").Show
    'Select Range A101.
    Range("A101").Select
End if

'Insert the forms text box value into that sheet.
Selection.Value = "DATA_3"

**sivdin** · 05-17-2011, 10:48 AM

Hi, Thanks for the code,
But, whether that this code fills the data in sheet1 or sheet2.
I require the dat to be filled in sheet1, after that cell focus should be in sheet2

**Domski** · 05-17-2011, 10:56 AM

As I said you can't select or activate a very hidden workbook. You didn't answer my question as to what you wanted to do but I'm guessing this might help...

Private Sub cmdSUBMIT_CLICK()
With Sheets("Sheet1")
    If cboName.Value = "MICHEL" Then
        .Range("A4").Value = "Something"
    ElseIf cboName.Value = "ANDY" Then
        .Range("A101").Value = "Something else"
    End If
End With
End If

Dom