Scroll Bar in Userform

[rory]

I would have thought that this:

Code: Select all

Spreadsheet1.ActiveSheet

made it clear? ;)

[adam]

Where should I place the code if you don't mind me asking? In a standard module??

[rory]

In the form's module.

[adam]

I did place the code in the forms module by replacing the line as

Code: Select all

 shtCurrent.Columns(1).Locked = True

Inorder to lock the column A. But still I could type in the column and delete the contents from the spreadsheet. What I have done wrong?

[HansV]

Did you also enable protection, as indicated by Rory?

[adam]

Do you mean the spreadsheet or the worksheet?? If I protect the spreadsheet control I get runtime error. I haven't protected the worksheet also.
With Rorys code I could write in the columns referenced in the the spreadsheet in user form but the values does not get copied into the worksheet.

I'm trying to get a code that would not allow the user to write in the protected columns of the spreadsheet. as they have formulas they need to be updated from the referenced cells. and also get copied to the worksheet.

I hope I have detailed what I'm searching for.

Thanks in advance.

[rory]

Did you actually run the routine that locks the column and protects the spreadsheet control?

[adam]

I'm sorry I coudn't get you. I would be happy if you could be more specific. Sorry for being unable to understand.

[rory]

The code I posted was an example routine. It has to actually be run in order to do anything - how did you run it (if you did)?

[adam]

Thanks a million Rory I have finally figured out how the code works and I've put the code under the Userform_Initialize event making the cells to be locked as the user form loads. Now I could move from cell to cell with the help of the tab button on the keyboard.

[adam]

How could the spreadsheets be switched with the help of a macro code.
The following code does not seem to work,

Code: Select all

Private Sub cmdOpen_Click()
Me.Spreadsheet2.View
End Sub

[rory]

Are you trying to switch sheets in the same control, or make a different control visible? (your code makes no sense, so I can't tell)

[adam]

What I'm trying to do is that when the user form is loaded the sheet 1 to be visible in the spreadsheet control. When the click of a macro button I want to switch to sheet 2 in the same spreadsheet control which would be referenced to another worksheet in my workbook.

In short; a macro code that would switch from Sheet 1 to Sheet 2 of the same spreadsheet control.

I hope this details my question.

[adam]

Thanks for th ereply Rory. I got it solved. Here's the code

Code: Select all

Private Sub cmdActivate_Click()
Spreadsheet1.Worksheets("Sheet2").Activate
End Sub