Moving thru locked cells

[iamtheeggman]

I have a protected sheet which the user inputs info in cells in columns B, D and F. In row 56 is a merged cell across A thru G. Using enter to move from cell to cell I am only able to move thru the unlocked cells in column B and into the merged cell and back to the first unlocked cell in B again. It should move thru the cells in B first then D then F. I guess the merged cell is causing the problem but is there a way to go thru the cells in B then the cells in D then the cells in F and finish in the merged cell at the end. The user puts daily sales numbers in the cells and uses the big merged cell to record notes about anything which may have happened during the day. ANY THOUGHTS??

 

[erik.van.geit]

it solved all of my problems

that's good news

Runtime error '1004'
Method 'OLEObjects' of object 'worksheet' failed.

that's clear info, we'll make progress
do you actually have textboxes named textbox1 on each sheet ?

 

[iamtheeggman]

Textboxes

I don't believe so because I don't recall naming the text boxes, Just drawing them and using them as is - how can I check.

 

[erik.van.geit]

just click them and look into the namebox
you will need to switch to edit-mode
therefore click on the control-toolbar on the leftuppericon

 

[iamtheeggman]

textboxes

sure enough that was the problem - they were text box 21 or 36 - I'm guessing that if they were all 21 I could chage the code to Textbox 21 or else rename the boxes to textbox 1 I will try that and see if it works.

Thanks again.

 

[erik.van.geit]

Perhaps you would like to create textboxes using code: so you would get all the same
just give them a name for future reference
you can also delete all others (if you do not want this, then quote out the line Call delete_objects(sh))
you will be happy with this
just edit:
1. topleftcell where the box will be (B57)
2. name: "ttttt"
3. :Width =.Width * 3, Height:=.Height * 10
replace the blue part with a number or replace 3 and 10
now the width will span 3 cells (if all columns have same width) and height 10 cells

Option Explicit

Dim obj As Object

Sub add_textbox_on_each_sheet()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Sheets
Call delete_objects(sh)
    With sh.Range("B57")
    Set obj = sh.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=.Left, Top:=.Top, Width:=.Width * 3, Height:=.Height * 10)
    obj.Name = "ttttt"
    End With
Next sh

End Sub

Sub delete_objects(sh As Worksheet)
For Each obj In sh.Shapes
      If obj.Type = msoOLEControlObject Then
        If TypeName(obj.OLEFormat.Object.Object) = "TextBox" Then obj.Delete
      End If
Next obj
End Sub

best regards,
Erik

 

[iamtheeggman]

Thanks

Thanks - as always a huge help!!!