Info from User Form to Data sheet and back

[Bafa]

Info from User Form to Data sheet and back:

I have never built a User Form. It seems like the functions I nee
are straight forward and simple, but they may not be as I am no
finding a tutorial on how to do it. Just this past year I learned ho
to use =IF statements in my calculations in a standard worksheet. S
it's obvious I am still very new to this and only know what I know b
deconstructing other sheets to see how they work. That being sai
please don't assume I know coding or anything beyoind the most basic.

I have almost completed a workbook for calculating data for a qualit
assurance we run for our computerized tomography unit. It has 3 pages
User Input which will be visable, and calculations page and Print Ou
page which will both be invisible. The User Input page is raw an
rough looking in its table form and I am afraid it will scare off othe
who don't feel comfortable in using it. I want to set up a user form s
they have a nice little window with input boxes and "next" and "back"
buttons to walk them through it all.

I don't know how to link input boxes to specific cells in my workshee
so that the calculations can be performed. Also there will be som
calculations performed in my worksheet which I need to show up in m
User Form so my co worrker will know what values to set to perform th
next step in the test. I am planning quite a few windows with next an
back buttons so that I can take them through step by step. Once al
calculations have been completed I need them to print out using all th
info that was input throughough the entire process.

I have not completed my workbook yet, but if needed I could post a lin
to my workbook so that anyone wiling to help me could get an idea o
what I am working towards and what exactly I need. Also after the en
of it all and the page is printed out I do not need to save any of th
info, since next week it will be all new info and calculations.

If this post in in the wrong spot feel free to move it. I tried t
read around to see where it fit best, but wasn't sure. Thanks for an
help

 

[Gary Keramidas]

here's a simple explanation, to get you started. i am guessing you can insert a
userform and add a textbox and a command button.

in a blank workbook:

right click the userform in the editor and choose view code
the dropdown in the upper left will display userform
select activate in the dropdown on the right where it says click

in between the sub and end sub it creates copy this

Me.TextBox1.Value = Worksheets("sheet1").Range("a1").Value

view the userform again and double click the command button
enter this code between the sub and end sub

Worksheets("sheet1").Range("a1").Value = Me.TextBox1.Value


now run the form , type something in the text box and click save, it will appear
in A1 on sheet1
close the form and reopen it, the textbox will show the value of sheet1 a1

 

[halimnurikhwan]

hi Bafa,

Ok, just post your book ... and I will try to see and help!

example in event of textbox :
Private Sub textBox1_Change()
range("A1").value = textbox1.value
End Sub

that will change range A1 value too while textbox1 value changed

or you're using commandbutton to react the value
Private Sub CommandButton1_Click()
range("A1").value = textbox1.value
End Sub

it will change range A1 value after commandbutton1 clicked

Rgds,

Halim

Gary Keramidas menuliskan:

 

[Bafa]

You guys are my heros! I never thought I would understand any of this
code, but this simple example you gave me helped me understand the
basics so I can apply it to my workbook.

So now I have the ability to enter data from my User Form into my
worsheet like I want. My work sheet will take this data and run them
through an equation and I need the results to appear as text in a label
on the next page of my user form.

For example I will need the cell from Sheet1 E38 to show up like this
in a label:

"Now set the Window Width to 1 and the Window Level to [Sheet1 E38] and
measure the diameter of the center dot."

In addition to that can you also please give me the command button code
that will close UserForm1 and open UserForm2?:



This is what I have now for my first suer form and it works great.

Private Sub CommandButton1_Click()
Worksheets("User Input").Range("d11").Value = Me.TextBox1.Value
Worksheets("User Input").Range("d12").Value = Me.TextBox2.Value
Worksheets("User Input").Range("d13").Value = Me.TextBox3.Value
Worksheets("User Input").Range("d14").Value = Me.TextBox4.Value
Worksheets("User Input").Range("d15").Value = Me.TextBox5.Value
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Click()
Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value
Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value
Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value
Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value
Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value
End Sub

Soon I will post a link to my workbook, but I still have basic equation
editing to do, so there is no point yet. Again thanks so much.

 

[Gary Keramidas]

you can use:

unload userform1
userform2.show

 

[halimnurikhwan]

Hi Bafa,
May be you can replace your :
Private Sub UserForm_Click()
Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value
Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value
Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value
Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value
Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value
End Sub

'to:
Private Sub UserForm_Activate()
TextBox1.Value = sheets("User Input").Range("d11").Value
TextBox2.Value = sheets("User Input").Range("d12").Value
TextBox3.Value = sheets("User Input").Range("d13").Value
TextBox4.Value = sheets("User Input").Range("d14").Value
TextBox5.Value = sheets("User Input").Range("d15").Value
End Sub

So you will get the value while Userform1 loaded and showed up

Gary's right he shows you to open a Userform like:
'place in a standard module :
Sub ShowForm()
UserForm1.Show vbModeless
End sub

Vbmodeless will allow you to interact with main application "Excel",
remove it if you won't !

To change Label1 Caption:
Private sub CommandButton2_Click()
Label1.Caption = Sheets(1).Range("E38").Text
End Sub

Or:
Private sub CommandButton2_Click()
Label1.Caption = "Now set the Window Width to 1 _
and the Window Level to _
[Sheet1 E38] and measure the
diameter of the center dot."
End Sub


Rgds,

Halim

Bafa menuliskan:

You guys are my heros! I never thought I would understand any of this
code, but this simple example you gave me helped me understand the
basics so I can apply it to my workbook.

So now I have the ability to enter data from my User Form into my
worsheet like I want. My work sheet will take this data and run them
through an equation and I need the results to appear as text in a label
on the next page of my user form.

For example I will need the cell from Sheet1 E38 to show up like this
in a label:

"Now set the Window Width to 1 and the Window Level to [Sheet1 E38] and
measure the diameter of the center dot."

In addition to that can you also please give me the command button code
that will close UserForm1 and open UserForm2?:



This is what I have now for my first suer form and it works great.

Private Sub CommandButton1_Click()
Worksheets("User Input").Range("d11").Value = Me.TextBox1.Value
Worksheets("User Input").Range("d12").Value = Me.TextBox2.Value
Worksheets("User Input").Range("d13").Value = Me.TextBox3.Value
Worksheets("User Input").Range("d14").Value = Me.TextBox4.Value
Worksheets("User Input").Range("d15").Value = Me.TextBox5.Value
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Click()
Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value
Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value
Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value
Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value
Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value
End Sub

Soon I will post a link to my workbook, but I still have basic equation
editing to do, so there is no point yet. Again thanks so much.

 

[halimnurikhwan]

To close a userform:
Private Sub CommandButton3_Click()
Unload Me
End Sub

Or :
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

Or:
Private Sub CommandButton2_Click()
Me.Hide
End Sub

Rgds,

Halim

(e-mail address removed) menuliskan:

Hi Bafa,
May be you can replace your :
Private Sub UserForm_Click()
Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value
Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value
Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value
Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value
Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value
End Sub

'to:
Private Sub UserForm_Activate()
TextBox1.Value = sheets("User Input").Range("d11").Value
TextBox2.Value = sheets("User Input").Range("d12").Value
TextBox3.Value = sheets("User Input").Range("d13").Value
TextBox4.Value = sheets("User Input").Range("d14").Value
TextBox5.Value = sheets("User Input").Range("d15").Value
End Sub

So you will get the value while Userform1 loaded and showed up

Gary's right he shows you to open a Userform like:
'place in a standard module :
Sub ShowForm()
UserForm1.Show vbModeless
End sub

Vbmodeless will allow you to interact with main application "Excel",
remove it if you won't !

To change Label1 Caption:
Private sub CommandButton2_Click()
Label1.Caption = Sheets(1).Range("E38").Text
End Sub

Or:
Private sub CommandButton2_Click()
Label1.Caption = "Now set the Window Width to 1 _
and the Window Level to _
[Sheet1 E38] and measure the
diameter of the center dot."
End Sub


Rgds,

Halim

Bafa menuliskan:

You guys are my heros! I never thought I would understand any of this
code, but this simple example you gave me helped me understand the
basics so I can apply it to my workbook.

So now I have the ability to enter data from my User Form into my
worsheet like I want. My work sheet will take this data and run them
through an equation and I need the results to appear as text in a label
on the next page of my user form.

For example I will need the cell from Sheet1 E38 to show up like this
in a label:

"Now set the Window Width to 1 and the Window Level to [Sheet1 E38] and
measure the diameter of the center dot."

In addition to that can you also please give me the command button code
that will close UserForm1 and open UserForm2?:



This is what I have now for my first suer form and it works great.

Private Sub CommandButton1_Click()
Worksheets("User Input").Range("d11").Value = Me.TextBox1.Value
Worksheets("User Input").Range("d12").Value = Me.TextBox2.Value
Worksheets("User Input").Range("d13").Value = Me.TextBox3.Value
Worksheets("User Input").Range("d14").Value = Me.TextBox4.Value
Worksheets("User Input").Range("d15").Value = Me.TextBox5.Value
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Click()
Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value
Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value
Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value
Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value
Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value
End Sub

Soon I will post a link to my workbook, but I still have basic equation
editing to do, so there is no point yet. Again thanks so much.

 

[Bafa]

Thanks again! hehe With that I can even get rid of my enter button and
just have "Next" and "Back" buttons.

Solved:
1. User form sending info to worksheet
2. Navigating userforms by command button

Still need:
1. Retrieve cell info from worksheet so it will show that current info
in a label box
2. Is there a way to have an icon on desk top that will allow you to
run only the User Form window and not have to get them to deal with the
data sheets and choose "Run" from excel to activate the User Form?

 

[halimnurikhwan]

Hi,
May be this is my last post today,
Still need answer :
1. We can retrieve anything about cells info, just determine what! like
:

Sub CommandButton4_Click()
Label1.Caption = ActiveCell.Interior.Colorindex
End sub
That will retrieve the colorindex of an activecell.

2. We can also run a Userform iconic, but Excel Application still get
open too
but we can make it unvisible but that's not a nice decision! It
will be userform only
viewed without Excel Applcation or Worksheet visible ... just
Application.Visible = false
so Excel will unvisible, only a userform is visible if we show the
userform first !
because if we make Application.Visible = False so it will be unsafe
for other Workbook
that currently opened too!
Unless we make :

Dim Appl as Excel.Application
Sub NewApp()
Set Appl = CreateObject("excel.application")
with Appl
.Workbooks.add
.Visible = false
end with
end sub

But unfortunately Appl will Visible = True if we open a Workbook
after Sub NewApp
because Excel Application is knowing Application last index window
that will be the
parent of a new opened Workbook. They still react each other ...


Rgds,
<smily>
Halim


Bafa menuliskan:

 

[Bafa]

Okay I have been going over the answers given to me and I am having
hard time. Like I said this is all new stuff for me. I have zer
experience with VB. I am grateful to all who have replied.

I am posting a link to a very raw beginings of my worksheet. I am b
no means asking for anyone to complete or do my work for me. But
have listed a few things in the VB User Forms that I want to have sho
up and can't seem to do correctly even with your instruction. I d
okay breaking down stuff I see in action. If anyone could fill ou
just a field or two so I can see how it differs from the code withou
it would help me to be able to further complete my project.

I really do wish I knew someone in real life that I could sit down wit
and have them show me a few things. I was even asking my co-workers t
refer me to someone who knows Excel, to which they replied that i
would be me. lol

http://www.smugglersrift.com/pctools/ctqav1.xls

I know you guys are giving me the right codes and such, I am just
dolt and insderting it properly. Thanks for the help