I can't seem to make a value appear in a text box in a User Form, when the User Form is opened.

[RockandGrohl]

Hi all,

I have a userform which opens and needs to display a date value in a text box at the top. The box is called "DateBox"

I'm unsure on whether this is the right code, and where it needs to go.

The Sub "Automationopen" loads "AutomationCP"

AutomationCP has a module called "Datebox_Change()"

And my code is as follows:

AutomationCP.DateBox.Text = Cells(2, ActiveCell.Column).Value

I've also tried .Value instead of .Text but nothing happens.

I don't get an error message, just nothing happens. Any ideas? Thanks.

 

[mumps]

Try adding this macro to the userform code module:

Private Sub UserForm_Initialize()
    Me.DateBox.Text = Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").Cells(2, ActiveCell.Column).Value
End Sub

Change the sheet name (in red) to suit your needs.

 

[RockandGrohl]

Try adding this macro to the userform code module:

Private Sub UserForm_Initialize()
    Me.DateBox.Text = Sheets("[COLOR=#FF0000]Sheet1[/COLOR]").Cells(2, ActiveCell.Column).Value
End Sub

Change the sheet name (in red) to suit your needs.

I've set "Paper Allocation" to "pa" and have amended my code to:

Me.DateBox.Text = pa.Cells(2, ActiveCell.Column).Value

But this still does not place any value in the text box.


I fear I'm putting it in the wrong place, it's in the Userform Code, in a sub called "Datebox_Change"


Nevermind, I fear I misread your post, trying it in Userform_INitialize now

 

[RockandGrohl]

Ah, this is working, but I found a problem!

The Cells(2) denotes whatever is on the 2nd row of the column I'm currently in.

The problem is that where it encounters a merged cell, it shows nothing!

I have a date between columns CJ and CO merged in row 2, but I only get the date if my active cell is in column CJ. Otherwise it's blank.

How do I get around this?

 

[RockandGrohl]

Ah, this is working, but I found a problem!

The Cells(2) denotes whatever is on the 2nd row of the column I'm currently in.

The problem is that where it encounters a merged cell, it shows nothing!

I have a date between columns CJ and CO merged in row 2, but I only get the date if my active cell is in column CJ. Otherwise it's blank.

How do I get around this?

Sorted it, the lazy + long way:

Set pa = Worksheets("Paper Allocation")



Do Until Cells(2, ActiveCell.Column).Value <> ""
    ActiveCell.Offset(0, -1).Activate
Loop
wcd = Cells(2, ActiveCell.Column).Value
ActiveCell.Offset(0, 3).Activate








Me.DateBox.Text = wcd

 

[mumps]

Glad it worked out. You should avoid merging cells at all cost because they create problems for macros.

 

[RockandGrohl]

Glad it worked out. You should avoid merging cells at all cost because they create problems for macros.

Yeah, unfortunately this is the way this sheet is for good now, not me who made it.


The new challenge is to make a message box appear when the user has a cell selected in an invalid column.

I've got this:

If Not IsDate(Cells(2, ActiveCell.Column).Value) Or Cells(2, ActiveCell.Column).Value = "" Then

    MsgBox "[TELLING OFF HERE]"


End If

The idea is, if the cell in row 2 is NOT a date and is NOT blank, then tell the user off.

Otherwise, if the cell is either a date or is blank (which is when it's merged, it's technically blank, right?) then proceed to show the userform.

However, it still tells me off when I click in one of the applicable columns as mentioned in my original post. If I click on the left-most column of the applicable columns, I don't get told off. Any ideas?

 

[mumps]

Without seeing your actual file, it's hard to tell. My suspicion is the merged cells.

 

[RockandGrohl]

Ah, solved it with this:

If Not IsDate(Cells(2, ActiveCell.Column).Value) And Not IsEmpty(Cells(2, ActiveCell.Column).Value) Then

    MsgBox "Derp"


End If

Sorted, if I try and open the invalid column it displays this error message, but otherwise it's fine

 

[RockandGrohl]

Actually, spoke too soon. If I click an invalid cell and open the Userform, it shows the error box, but I need it to then close the sub down where instead it's showing me the error box, then showing the (blank) userform.