DIALOG BOX

[Gabriela]

Hi i have created a dialog box with a few entries such as Name, Sex among others.

I want the information to be pasted on another sheet . how can i do it?
I know how to put it on the same sheet of the formula ..

tnx

 

[cfree36]

Sub PASTE()
Dim Name As String
Dim Sex As String

Name = Application.InputBox("What is your name?", "Enter Name Here")
Sheet2.Range("A1").Value = Name
Sex = Application.InputBox("Are you Male or Female?", "Enter Sex Here")
Sheet2.Range("A2").Value = Sex

End Sub

Change Sheet2 to the sheet number you want to paste your data and change the cells to where you want the data to go.
Note: Sheet2 is not the sheet's name, but rather it's sheet code

Sheet2 ("Main Doc")
Sheet2 is the code
Main Doc is the Sheet's Name

 

[Zack Barresse]

Hi there, welcome to the board!

You only need to explicitly reference the sheet. In my opinion, you should reference a sheet by either it's index or name. An example is ..

Sheets("Sheet1").Range("A1").Value = Me.TextBox1.Value

This is of course assuming that you are using a UserForm and want A1 on Sheet1 to equal TextBox1.

HTH

 

[cfree36]

Firefytr:

Why the preference to sheet name or index number? What are the drawbacks to using the code name of a sheet in your code?
I have been using the code name so if the sheet is either moved, or renamed the VBA Code still works.
I am not questioning your thinking... just trying to learn from it.

Thanks.

 

[Gabriela]

dialog box

Its not about changing the sheet name...I want the information entered into a dialog box to be shown on a specific sheet...

hope no to question ur thinking to.

tnx

Firefytr:

Why the preference to sheet name or index number? What are the drawbacks to using the code name of a sheet in your code?
I have been using the code name so if the sheet is either moved, or renamed the VBA Code still works.
I am not questioning your thinking... just trying to learn from it.

Thanks.

 

[Gabriela]

Tnx a lot

First i want to tnk u 4 the help

But what if i wanna look in the Sheet2 for an empty cell, cause im going to create some reports on the information entered.

Tnx

Sub PASTE()
Dim Name As String
Dim Sex As String

Name = Application.InputBox("What is your name?", "Enter Name Here")
Sheet2.Range("A1").Value = Name
Sex = Application.InputBox("Are you Male or Female?", "Enter Sex Here")
Sheet2.Range("A2").Value = Sex

End Sub

Change Sheet2 to the sheet number you want to paste your data and change the cells to where you want the data to go.
Note: Sheet2 is not the sheet's name, but rather it's sheet code

Sheet2 ("Main Doc")
Sheet2 is the code
Main Doc is the Sheet's Name

 

[Zack Barresse]

I'm curious how you 'created a dialog box'. Do you mean UserForm?

cfree36, I'm not saying that using a sheets code name is a bad thing, it's just not my personal preference. It can be difficult at times when dealing with code names from other workbooks and I find it to be more cumbersome. There really isn't anything wrong with what you posted, just not the way I do things.

There are many ways to skin a cat.

 

[Gabriela]

tnx

Ok im not an excel expert can u tell me how u do things?

Maybe that would help

I'm curious how you 'created a dialog box'. Do you mean UserForm?

cfree36, I'm not saying that using a sheets code name is a bad thing, it's just not my personal preference. It can be difficult at times when dealing with code names from other workbooks and I find it to be more cumbersome. There really isn't anything wrong with what you posted, just not the way I do things.

There are many ways to skin a cat.

 

[Zack Barresse]

Well it sounds like a UserForm. To create one ..

Hit Alt + F11, to enter the VBE
Hit Insert | Userform (from menu)
Create a TextBox for Name
Create an according label for it
Create a ComboBox for Sex
Create an according label for it
Create two CommandButton's (one for Cancel, the other for Ok)
Double click your command buttons to enter code for it.

This assumes you have the following controls created:
TextBox1 - for name
ComboBox1 - for sex
CommandButton1 - for cancel
CommandButton2 - for ok
(labels as desired)

From there, your code might be something like this...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
    <SPAN style="color:#00007F">If</SPAN> Me.TextBox1.Value = "" <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Please fill out a name!", vbInformation
        Me.TextBox1.SetFocus
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2") <SPAN style="color:#007F00">'set as desired</SPAN>
        .Range("A1").Value = Me.TextBox1.Value
        .Range("B1").Value = Me.ComboBox1.Value
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
    Me.ComboBox1.AddItem "Male"
    Me.ComboBox1.AddItem "Female"
    Me.ComboBox1.ListIndex = 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


And in a seperate Standard Module (Insert | Module) you can have code to call your form like so ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ShowForm()
    UserForm1.Show
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


To assign this code to a button, from the Forms menu create a Command button, it will automatically prompt you to Assign Macro, choose ShowForm (the one you should have installed [above]) and click Ok.

Is this what you're looking for?