Re: Put/Place Cursor Into UserForm TextBox
Isolating the simple code to a userform with Textbox1 on page 1 of a multipage control... the code works fine.
There's something else in your code/form that's causing your problem.
Re: Put/Place Cursor Into UserForm TextBox
Isolating the simple code to a userform with Textbox1 on page 1 of a multipage control... the code works fine.
There's something else in your code/form that's causing your problem.
So do we love it or hate it?
I don't think I've used it enough to render a final sentence yet...
My current impressions are:
In a program that I'm intimately familiar with (Excel) I seem to find it annoying. ...or at least in my limited usage thus far it's been that way. Takes up a lot of screen real estate and seems to be a bit of a cluttered jumbled mess of text and graphics.
For programs I use less frequently (Publisher) it seems to be helpful. I can sorta right-click my way through lots of things and get stuff done. That's kinda nice.
For a program I use moderately (Word) it's sort of a toss-up. I can't find stuff easily, but once I figure it out, it seems like it's a more efficient design.
Re: Protect Cells But Allow Changes Through Userform
Run macros on protected sheets
http://www.ozgrid.com/VBA/excel-macro-protected-sheet.htm
Re: Protect Cells But Allow Changes Through Userform
Easiest way is to protect all sheets of the workbook at startup and invoke the UserInterfaceOnly property.
That way you don't have to be toggling the protection state in your userform code... which can open the possibility for leaving the sheets unprotected if an unhandled error occurs before reprotection is applied.
Re: Dragging Formulae
It's possible... depends on how complicated you want it to look.
You could do some sort of offset or transpose nested inside the function.
An easier way would be to just add a new sheet called "Transpose" and create a simple non-array formula to transpose the row data to columns and then base your Sheet1 formula on the transposed data and drag copy as you normally would.
Re: Change Cell Font Type If Condition True
Just another thought...
Would a simple format like this work?
Format|Cells|Shrink to Fit
Re: Change Cell Font Type If Condition True
PCI's code is a simple worksheet calculate event procedure. It would go in the worksheet objects code module.
Is column "F" the result of a formula or is that where the actual data entry takes place?
Re: Dashes In Formulas...
If (for whatever reason) you don't want to enter the apostrophe...
You can select the column and set the cell format to "Text". I don't usually recommend it; as people tend to forget it's set that way and then don't understand why values no longer sum and so forth...
Holding ctrl-while draggin the fill handle is one option. The other is to select the range and enter "4-5" or "'4-5" and then press ctrl-enter to array-enter the value across all cells of the selected range. I find myself doing this a lot when I need to set all values of an input range to zero in a financial model.
Re: Change Font Type If Cell Condition Is True
Couple options...
1) Write your own code to do it.
2) Use the BYO Conditional Format example on my website (which allows for both using different fonts and n conditions)
There is not a standard setting in Excel for changing the font as part of a conditional format.
Question is: Do you really need to change the font to highlight a condition? ...or could you just change the background color, in which case the standard conditional formatting would be suitable as-is. Using same font but toggling the BOLD setting is also doable.
Re: Force Scrollbar In Textbox To Top
You'll notice that a userform has a property called: "KeepScrollBarsVisible"
It would seem logical that IF the standard textbox control had such a property, it would be called the same... unfortunately, it doesn't. So the defeatest answer would be, "You just can't do that."
However...
We do have an independent scrollbar control that we can play with.
To get the kind of functionality you're looking for, you'll probably have to disable the textbox scrollbar and then code your own scrollbar control to manipulate the textbox the way you want. That's the only way I can think of to allow the scrollbar to remain visible in VBA.
On a userform with Textbox1 and Scrollbar1 controls the following code would associate scrollbar movement with the textbox.
Private Sub UserForm_Initialize()
TextBox1.SetFocus
ScrollBar1.Max = TextBox1.LineCount - 1
ScrollBar1.Min = 0
ScrollBar1.Value = ScrollBar1.Min
TextBox1.CurLine = ScrollBar1.Value
End Sub
Private Sub ScrollBar1_Change()
TextBox1.SetFocus
TextBox1.CurLine = ScrollBar1.Value
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
ScrollBar1.Value = TextBox1.CurLine
End Sub
Display More
It is important to remember that Textbox1 must have focus before trying to assign or set Textbox1.Curline values... Thankfully, the scrollbar doesn't seem to mind that the focus keeps shifting back to the textbox.
Re: Interactive Game
There are a whole bunch of games on my website in the downloads section... (check my signature for link)
The minesweeper clones are math/logic-ish type games. There are also a few pure number games in there. My Mosaic game is a sliding picture puzzle game. Look around and see if you find anything useful... there are also several yahtzee and mastermind games.
Re: Avoid multiple select constructs
I would manage it something like this:
Dim TxtBox_Coll As New Collection
With TxtBox_Coll
.Add TextBox1
.Add TextBox2
.Add TextBox3
.Add TextBox4
.Add TextBox5
End With
Dim tbox As msforms.TextBox
For Each tbox In TxtBox_Coll
Select Case tbox.Value
Case 2 To 4
tbox.BackColor = &H80FFFF
Case Else
tbox.BackColor = &HFF
End Select
Next tbox
Display More
Re: Insert Blank Row Above Current Row
If you record a macro it will give something like this:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/23/2008 by Aaron
'
'
Selection.EntireRow.Insert
End Sub
Since it references "Selection" and not a specific range it should work just fine. Somehow you're recording an action that is producing a specific range reference. Just change the Range references to "Selection" or "ActiveCell" and it should work for whatever cell you have selected.
Re: Offset Based On Cellvalue
I thought that bit was self-evident...
If you want... you could substitute the formula for r in place of r in the offset method.
...or if you're OK with shortcutting a bit then:
Re: Transpose Every Nth Cell Range
I was under the impression that you wanted that extra row delete option incorporated into the previous bit of code. I did modify the name of the macro to indicate what actions it was performing.
If you wanted to strip it down to just the delete-loop action then:
Re: Selecting Adjacent Cells
You can use offset in combination with resize, or you can directly specify the range by nesting the references inside another range method.
@ lenze...
Whether or not you actually "Select" the range is at your discretion. I merely used it as a tidy way to complete a simple single line of code concept. Hopefully, in the above line no one will criticize the doubled hard code "C10" reference (generally something I wouldn't do) again, just trying to convey a concept.