Custom Dialog Box, Command_Box and variable passing problems

[Sal Paradise]

I've been shamelessly using this site as a reference for my diddling with my work spreadsheets for a while now, but I think I have come to an impasse as I try to tackle something well above what my level seems to be. I have several questions, which I will do my best to pose in an easy-to-understand-way, and hope that they aren't excessively simple.

I am trying to create UserForms in VBA for the automatic entry of information. The current userform I'm making is supposed to copy a hidden blank row, insert it to the top of the spreadsheet (with formatting and formulas intact), then create a dialog which gives 4 dropdown menus and a text field which will automatically fill in the proper entries in the new row.

My problem is two-fold:
1) Passing Data between the Dialog Box and the Module in which I will use the data collected and copy it into the proper location.
2) Controlling the Data Box and when it appears

I have a dropdown menu. It has 5 choices in it. Once I select a choice, I want that choice to be copied into a public variable. I want to then take that public variable and enter it into a specific cell in the spreadsheet after the dialogue has closed. I use a button at the bottom to hide the userform after picking the data to be entered.

Here is my code for the UserForm:
(NewEntry is the name of the UserForm, NewJob is the name of the Dropdown Command_box, MakeNewEntry is the name of the command button that's supposed to make the menu go away)

Public Sub UserForm_Initialize()
    NewEntry.NewJob.AddItem "new"        'ListIndex = 0
    NewEntry.NewJob.AddItem "as-is"        'ListIndex = 1
    NewEntry.NewJob.AddItem "big"        'ListIndex = 2
    NewEntry.NewJob.AddItem "mid"        'ListIndex = 3
    NewEntry.NewJob.AddItem "small"        'ListIndex = 4
End Sub

Public Sub NewJob_Change()
    Select Case NewJob.Value
    Case 0
    Module1.VarNewJob = NewEntry.NewJob.Text
    Case 1
    Module1.VarNewJob = NewEntry.NewJob.Text
    Case 2
    Module1.VarNewJob = NewEntry.NewJob.Text
    Case 3
    Module1.VarNewJob = NewEntry.NewJob.Text
    Case 4
    Module1.VarNewJob = NewEntry.NewJob.Text
    End Select
End Sub

Public Sub MakeNewEntry_Click()
    NewEntry.Hide
    MsgBox (Module1.VarNewRegion)
End Sub

Here is the code for the Module:

Sub NewRow()
    Rows("11:11").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.EntireRow.Hidden = True
    NewEntry.Show
    MsgBox ("Region: " & VarNewRegion & " Type: " & VarNewType & " Job: " & VarNewJob)
End Sub

Obviously nowhere near completed, because the MsgBox always returns blank values, and I don't know why. Am I making my references wrong? Pointing to the wrong place? Is there a nice and simple tutorial on creating userforms somewhere on the internet that I should take a look at instead?

I can get the dialog box to populate itself, but I can't get the data entered to move out of the userform, or to manipulate the userform with any expertise. Driving me nuts it is, and it seemed so easy before I started.

 

[acw]

Hi

Don't have to make things quite so convoluted.

Keep the initialise event code, but the other 3 can be compressed along the lines of

Public Sub NewJob_Change()
    Rows("11:11").Copy
    Rows("11:11").Insert shift:=xlDown
    Application.CutCopyMode = False
    Range("A12").Value = newjob.Value
    newentry.Hide
End Sub

Made some rough assumptions about where you are, what the current sheet is etc...

HTH

Tony

 

[Sal Paradise]

You are a gentleman and a scholar. That's a huge help. So Value is not equivalent to text -- go figure. Thank you very much.

Is there a quick and easy way to have the menu drop-down show a long name, but only enter a far shorter abbreviation into the cell? Such that, for instance, the drop-down menu reads

Massachusetts
Virginia
Connecticut
California
Hawaii

But when you select them, they enter
MA
VA
CT
CA
HI

Or am I just pushing my luck?

 

[acw]

Hi

1) With the first question, I don't think it was a matter of text or value. More likely the scope of the variables. You don't say, but my thought would have been that they were not global so they weren't being passed. With my approach, you didn't need to pass the values (or text) anywhere other than the output place.

2) Set up a mapping range somewhere that maps the 2 char code to the name. You can then use VLOOKUP to do the conversion before pasting to the cell. I'm assuming again that the drop down is on a form...


Tony


HTH

Tony

 

[Sal Paradise]

The drop-down is on a form. I think I'll just keep it simple, as I don't want to have to diddle with vlookup for the sake of style of my dropdown menus. Only 2 of us using the spreadsheet, and we know the abbreviations, so I s'pose it's not of critical importance.

Thank you again for the help.