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]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[RockandGrohl]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Hi mumps,

Understood, but I can't do that to this sheet, even on a copy, it's hard to explain but it's definitely too difficult to redact the information and still have it work, because a lot of the VBA is built around a dependency on cells containing certain names.

I think I've identified the problem, however.

When you press the "Automation Button" with the correct cell selected, it loads this:

Sub Automationopen()

[B]Load AutomationCP[/B]
With AutomationCP
.Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
.Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
.Show
End With




End Sub

Where it then loads AutomationCP, this is where the code is:

Private Sub UserForm_Initialize()

Set pa = Worksheets("Paper Allocation")


If Not IsDate(Cells(2, ActiveCell.Column).Value) And Not IsEmpty(Cells(2, ActiveCell.Column).Value) Then
    MsgBox "Please select a cell inside the date range"
    Exit Sub
End If










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


PapNam = Cells(ActiveCell.Row, "E").Value


Me.PapNamBox.Text = PapNam


End Sub

So what's happening is, when the "If Not" check fails, it displays a Message Box, the user clicks ok and it shows a blank userform, because upon End If, we return to the first bit of code where it then goes to the "With AutomationCP
Etc
etc
.Show"

So even though the sub is exiting, it still continues the rest of the first bit of code.

I need something to end all in one go..

And I just googled it after writing that last sentence, and all I had to do was change the "End Sub" after the message box, to "End"

And it does exactly what I want

 

[mumps]

Terrific!!