Solved: Excel 2011 - can not update locked textbox programmatically if locked

Greetings - just wondering if there is a better workaround than locking and unlocking a textbox to edit it programmatically. Excel 2011 does not play with locked textbox like the windows versions. On Windows (and 2004 I believe) you can update the textbox without issue via code, but the improvements in 2011 seem to have changed how locked textboxes are handled.

Of course, I could just unlock the textbox - but I have it locked and provide a scrollbar for people to update values.

I think this makes sense without code, but if someone wants to see it I can mock up a workbook to attach.

There is a post on the topic at http://answers.microsoft.com/en-us/m...a-7aacea26b1b6 , and the answer is to unlock the textbox. There is also an assertion that the 2011 actually handles textboxes correctly, and it is the win versions that have it wrong...I actually see the point the MVP there is trying to make, but wouldnt that be a first? :)

What kind of text box? Userform?
The attachment would be appreciated.

Can you describe what do you want the text box to do or not do?

I'm not familiar with Windows Excel, so a comparison to Windows isn't useful to me.

If you are using it only to display the value of a Scroll Bar, and not accept user entry, a Label might be a better choice for the control.

Alternatly, one could set a TextBox to only accept values in the ScrollBars range and link that back to the scroll bar.

Thanks - the label approach was one I had not considered previously!

This is a scrollbar linked to a textbox on a userform; it allows people to set a time, something I dont quite trust users to do correctly...but they want to type in a time, kinda hate using the scrollbar. I should try to allow using the scrollbar or typing in a time...sure wish there was an IsTime function, but it shouldnt be that hard to error check; just need to make sure there is a ':' and AM or PM...right?

Appreciate the reply and thoughts! I'll work on mocking up a workbook with the info, might be a couple of days though.

IsDate serves as an IsTime function.
You might try this code. Get a userform with a TextBox and a Scroll Bar and this code in the userform's module.

The ScrollBar1_Change and TextBox1_AfterUpdate events link those two controls.
The TextBox1_KeyPress event prevents the user from entering a non-time.
The TextBox1_AfterUpdate takes the user entered time and converts it to a standard format.
The UserForm_Intialize event setsup the limits on the scroll bar, depending on whether seconds or minutes is the shortest interval of interest.

[VBA]Dim formatString As String

Private Sub ScrollBar1_Change()
TextBox1.Text = Format(ScrollBar1.Value / ScrollBar1.Max, formatString)
End Sub

Private Sub TextBox1_AfterUpdate()
With TextBox1
If IsDate(.Text) Then
.Text = Format(TimeValue(.Text), formatString)
ElseIf IsDate(.Text & "a") Then
.Text = Format(TimeValue(.Text & "a"), formatString)
ElseIf IsDate(.Text & "0") Then
.Text = Format(TimeValue(.Text & "0"), formatString)
End If
ScrollBar1.Value = CDbl(TimeValue(.Text)) * ScrollBar1.Max
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim newText As String
With TextBox1
newText = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
End With
If Not (IsDate(newText) Or IsDate(newText & "0") Or IsDate(newText & "A")) Then
KeyAscii = 0
End If
End Sub

Private Sub UserForm_Initialize()
Rem format scrollbar for minutes
Dim minuteBased As Boolean, secondBased As Boolean
Dim timeFactor As Double
minuteBased = True: secondBased = False

If minuteBased Then
formatString = "h:mm AM/PM"
timeFactor = 24 * 60
ElseIf secondBased Then
formatString = "h:mm:ss AM/PM"
timeFactor = 24 * 60 * 60#
End If

With ScrollBar1
.Min = 0
.Max = timeFactor
.SmallChange = 1
.LargeChange = 60
.Value = 0
End With
End Sub[/VBA]

That's pretty slick, thank you! BTW, found your datepicker; very nice!

I've attached a workbook with an example - it works fantastic. Took some minor liberties with your original code, hope that is OK. I also added a checkbox to lock the textbox; on a mac, the textbox will not update on scroll if locked, on windows it will - although I have not tested this particular userform.

Thanks for taking the time, I hope someone else finds this as useful as I do!

This will work on either Windows or Mac.
[VBA]Private Sub ScrollBar1_Change()
Dim LockMemory As Boolean
With TextBox1
LockMemory = .Locked
.Locked = False
.Text = Format(ScrollBar1.Value / ScrollBar1.Max, formatString)
.Locked = LockMemory
End With
End Sub [/VBA]

Quote:

---

Originally Posted by tpoynton

BTW, found your datepicker; very nice!

---

Where did you find it? Do you still have the link?

In the attached, clicking on the seperate labels "8am","noon", etc. will adjust the time accordingly.

One of my pet peeves is the use of If..Then..Else to set boolean values. I altered the CheckBox1_Click event.
[VBA]Private Sub CheckBox1_Click()
With TextBox1
.Locked = Not (.Locked)
.Text = Format(ScrollBar1.Value / ScrollBar1.Max, formatString)
End With
End Sub[/VBA]

I found the date picker for mac searching the forum - http://www.vbaexpress.com/forum/showthread.php?t=38721 .

Thanks for improving the form even further! An annoyance you can see in the userform is that what is developed on mac looks good on mac with default settings, and what is developed on PC looks good on PC at default settings, but the default background color for userforms/label backgrounds does not convert like it did with 2004.

I love the clicking on labels to update, and the simple code to deal with mac/PC compatibility. Now if only the labels would line up across platforms without separate code...can probably fix it by adjusting the .left property of the labels easily enough. If you do not have windows, the issue is that scrollbars on macs put the arrow buttons on the right side, while on win there is an arrow on each side of the scrollbar.

THANKS AGAIN! Now to incorporate in my project :)

Hmm. on my Mac (Excel 2011) a ScrollBar has a button on each end.

One way to deal with the differences across platform is to set the default properties (like Font and Size) to something that is common to both.

Eg. on my Mac, the default Font.Name for a userform is Helvetica, which isn't on Winodows (or is very different), so if I know the UF is going to be used on a Windows machine, I'll specify Arial in the property window of the control.

That's a good idea to set the font; I think it is Tahoma on Windows.

I am also using Excel 2011, I swear it puts both buttons on the same side of the scrollbar! I have the same thing on all scrollbars it seems; it is also true for Firefox...ah, it seems like an option on the mac (http://www.dummies.com/how-to/conten...gs.html)...how to tell if the buttons are grouped or on different ends is way beyond my knowledge. Seems like I'll need to find a way to link label position to scrollbar position, or ditch that part! I did see that you did something with the labels via code, but I did not dig into it to see if it does anything with positioning.

What I did with the labels was...
In the unloaded user form, a label's caption is "8am" (for example).
When the uf is loaded (initialized) the routine takes that time, puts it in the .Tag property and changes the .Caption to "|" & vbCR & "8am".

The AnyLabelClick routine looks at the .Tag property of the clicked label and sets the Text box to that time and calls the TextBox1_AfterUpdate (which adjusts the scroll bar).

I did it that way so that the developer (you) can easily control the label's time.
Just change "8am" to "3:26pm" (if that's a time of interest to you).
Clicking on the label will change the time entered to 15:26.

Currently, changing the label's caption won't change it's position, that has to be adjusted "by hand".

I need to dig a bit, but to automate the label position, one would need to be able to read the physical extent of the scrollable portion of the scroll bar.... How to do that....???

Thanks for the explanation!

I tried figuring out a way to move the labels to accommodate for having both buttons on the same side, but gave up. With enough trial and error math I'm pretty sure one could find the middle of the scrollbar in reference to the userform...but when the buttons are both on one side, the middle of the scrollable area is not the middle of the scrollbar.

For my project, people will be happy enough to be able to type in times; the labels did not exist previously, so they will not be missed :)

I truly appreciate your time and expertise!