Input box

F

FiluDlidu

Hi all!

I'm looking for a way to have an input box popping up in a sheet I made.
Basically, my sheet is a learning tool that gives me words in a language and
ask me to translate them into another language.

Now, say it asks me for "horsse" to be translated into French. I would to
code a button that would allow me to modify entries whenever I find a
mistake. So in this case, the popup would contain "horsse" and a box where I
should type in what should replace it ("horse" in this case), as well as
"cheval" (its French translation) with its own box where I could type, if
there is a need, a corrected version for this word as well (let's say
"cheval, chevaux", both the singular and plural versions of the word).

Thank you kindly for any thought you might put at the problem,
Feelu
 
T

The Code Cage Team

How would you identify the word(s) that you should have presented to you?,
would you be selecting the words manually?, would you want the word(s) that
are replaced to go in the same cell as the translation?

To be honest if you are working with a lot of text and want spell check and
be able to identify and replace all occurrences you would be better off using
MS Word!

Regards,
The Code Cage Team
www.thecodecage.com/forumz
 
F

FiluDlidu

How would you identify the word(s) that you should have presented to you?

This I already took care of. Columns A to C contain respectively source
language words, target language equivalents and a number that helps the
question generator to pick more often those I miss more often. These three
columns are hidden and the game occurs in columns further to the right (H, if
I remember correctly, but it's not really important for my question).

Now, when the generator inputs a new word to translate, I want to be able to
correct it if I realize there might be a typo or something. But right now, I
need to unhide A:C, find the proper spot and fix it manually, then re-hide
A:C and keep playing. This is rather more tedious than what a button saying
"Fix mistake" would do, if only I were able to design it in such a way that
it would allow me to change both the source and target language input in the
same box...

Is this clearer???
 
T

The Code Cage Team

You say "generator"? how is this achieved?, if you have code for this then
please supply it where we will be able to look at adapting a spell check to
it and maybe adding an offset for the translation.

Regards,
The Code Cage Team
www.thecodecage.com/forumz
 
F

FiluDlidu

Thing is, the code is long long long, it contains several subs and I feel
like it is neither very meaningful to my specific problem, neither very
appealing to people to look at. If I were only getting the code to create
the popup window with two input boxes in it, then I am fairly confident I
could splice it to my work on my own, spending myself the few hours of work
it might represent instead of pushing this work onto someone else who most
likely have something better to do than trying to figure out my mess (also, I
am French speaking and the source language is French, the target language is
Hungarian and a lot of my variable names would not mean much to most people
here, so I tried to simplify my problem so that I would stand a chance to get
people interested in fixing it).

So imagine there is already a sub that inputs a value from A:A into H3 for
me to translate into H4.

I want a button in I4 that, when pressed, propose a popup window with the
value from H3 and a box beside it, where I could type any possible correction
to the value of H3, then with also the value from B:B adjacent to the cell in
A:A that contains the value of H3, with another box into which I could type
any possible correction to that value.

Then I could press 'Validate' or something like that and values from H3 and
the appropriate cells from A:B would be changed to whatever was typed into
the input box.

Can this be achieved simply?

Thank you very much for your interest,
Feelu
 
C

Code Cage Team

You can add a command button and call this code from it:
Sub pop_up_for_cell()
Dim ib As String
ib = InputBox("The word " & Range("H3").Value & " has been found" & Chr(13) _
& "either type you correction or click ok to accept!", "Word Check",
Range("H3").Value)
Range("H4").Value = ib
End Sub
 
F

FiluDlidu

Mmmh!

Two problems with this code:
1) It doesn't allow to change the target language value, were there a
mistake in it;
2) What is typed in doesn't change the value in H3, but only enters it in H4.

I found something, but it's rather tedious... Maybe you could try it to see
what it gives:

---

To correct H3 and its equivalent in A:B

Sub change()
Application.EnableEvents = False
Dim Title, Default1, Prompt1, Corrected1, Default2, Prompt2, Corrected2
Title = "Modify errors encountered"
Default1 = Range("H3").Value
Prompt1 = Default1 & " should become: "
Corrected1 = InputBox(Prompt1, Title, Default1)
If Corrected1 = "" Then Corrected1 = Default1
Default2 = Range("A:A").Find(Default1).Offset(, 1).Value
Prompt2 = Default1 & " became " & Corrected1 & Chr(10) & Chr(10) & Chr(10) &
Default2 & " should become: "
Corrected2 = InputBox(Prompt2, Title, Default2)
If Corrected2 = "" Then Corrected2 = Default2
If Corrected1 = Default1 Then
GoTo Target
Else: Range("A:A").Find(Default1).Value = Corrected1
Range("H3").Value = Corrected1
End If
Target:
If Corrected2 = Default2 Then
GoTo Last
Else: Range("A:A").Find(Corrected1).Offset(, 1).Value = Corrected2
End If
Last:
Range("H4").Select
Application.EnableEvents = True
End Sub

-----
To generate random values in H3, for us to translate into H4:

Sub enterNew()
Application.EnableEvents = False
Range("H3").Formula = "=INDEX(A:A,INT(" & Range("C1").Value & "*RAND())+1)"
Range("H3").Value = Range("H3").Value
Range("H4").Select
Application.EnableEvents = True
End Sub

---

Random stuff to put into A:C

horse cheval =counta(a:a)
cat chat
dog chien
raven corbeau
fox renard
wolf loup
 
F

FiluDlidu

Well, let's put this into shorter lines... (sorry for the sloppy first try!)

Sub change()
Application.EnableEvents = False
Dim Title, Default1, Prompt1, Corrected1, Default2, Prompt2, Corrected2
Title = "Modify errors encountered"
Default1 = Range("H3").Value
Prompt1 = Default1 & " should become: "
Corrected1 = InputBox(Prompt1, Title, Default1)
If Corrected1 = "" Then Corrected1 = Default1
Default2 = Range("A:A").Find(Default1).Offset(, 1).Value
Prompt2 = Default1 & " became " & Corrected1 & Chr(10) & Chr(10) _
& Chr(10) & Default2 & " should become: "
Corrected2 = InputBox(Prompt2, Title, Default2)
If Corrected2 = "" Then Corrected2 = Default2
If Corrected1 = Default1 Then
GoTo Target
Else: Range("A:A").Find(Default1).Value = Corrected1
Range("H3").Value = Corrected1
End If
Target:
If Corrected2 = Default2 Then
GoTo Last
Else: Range("A:A").Find(Corrected1).Offset(, 1).Value = Corrected2
End If
Last:
Range("H4").Select
Application.EnableEvents = True
End Sub

FiluDlidu said:
Mmmh!

Two problems with this code:
1) It doesn't allow to change the target language value, were there a
mistake in it;
2) What is typed in doesn't change the value in H3, but only enters it in H4.

I found something, but it's rather tedious... Maybe you could try it to see
what it gives:

---

To correct H3 and its equivalent in A:B

Sub change()
Krap! Please see above for a better version
End Sub

-----
To generate random values in H3, for us to translate into H4:

Sub enterNew()
Application.EnableEvents = False
Range("H3").Formula = "=INDEX(A:A,INT(" & Range("C1").Value & "*RAND())+1)"
Range("H3").Value = Range("H3").Value
Range("H4").Select
Application.EnableEvents = True
End Sub

---

Random stuff to put into A:C

horse cheval =counta(a:a)
cat chat
dog chien
raven corbeau
fox renard
wolf loup
Click to expand...
 
C

Code Cage Team

Nothing wrong with either of those codes, both do what you expected:
Sub change1()
Application.EnableEvents = False
Dim Default1, Corrected1, Default2, Corrected2
Default1 = Range("H3").Value
Default2 = Range("A:A").Find(Default1).Offset(, 1).Value
Corrected1 = InputBox(Default1 & " should become: ", "Modify errors
encountered", Default1)
If Corrected1 = "" Then Corrected1 = Default1
Corrected2 = InputBox(Default1 & " became " & Corrected1 & Chr(10) & Chr(10) _
& Chr(10) & Default2 & " should become: ", "Modify errors encountered",
Default2)
If Corrected2 = "" Then Corrected2 = Default2
If Corrected1 = Default1 Then
GoTo Target
Else: Range("A:A").Find(Default1).Value = Corrected1
Range("H3").Value = Corrected1
End If
Target:
If Corrected2 = Default2 Then
GoTo Last
Else: Range("A:A").Find(Corrected1).Offset(, 1).Value = Corrected2
End If
Last:
Range("H4").Select
Application.EnableEvents = True
End Sub
Regards,
The Code Cage Team
www.thecodecage.com/forumz


FiluDlidu said:
Well, let's put this into shorter lines... (sorry for the sloppy first try!)

Sub change()
Application.EnableEvents = False
Dim Title, Default1, Prompt1, Corrected1, Default2, Prompt2, Corrected2
Title = "Modify errors encountered"
Default1 = Range("H3").Value
Prompt1 = Default1 & " should become: "
Corrected1 = InputBox(Prompt1, Title, Default1)
If Corrected1 = "" Then Corrected1 = Default1
Default2 = Range("A:A").Find(Default1).Offset(, 1).Value
Prompt2 = Default1 & " became " & Corrected1 & Chr(10) & Chr(10) _
& Chr(10) & Default2 & " should become: "
Corrected2 = InputBox(Prompt2, Title, Default2)
If Corrected2 = "" Then Corrected2 = Default2
If Corrected1 = Default1 Then
GoTo Target
Else: Range("A:A").Find(Default1).Value = Corrected1
Range("H3").Value = Corrected1
End If
Target:
If Corrected2 = Default2 Then
GoTo Last
Else: Range("A:A").Find(Corrected1).Offset(, 1).Value = Corrected2
End If
Last:
Range("H4").Select
Application.EnableEvents = True
End Sub
Click to expand...
 
F

FiluDlidu

Yes and no... I still don't like the way it's bringing the user through two
input boxes rather than only one where both fixes could be made at once.
 
C

Code Cage Team

You will have to create a userform, add two textboxes and a command button,
once you have done that double click the command button and drop this in:
Private Sub CommandButton1_Click()
Dim rFound As String
rFound =
ActiveWorkbook.Sheets("Sheet1").Range("A:A").Find(What:=ActiveWorkbook.Sheets("Sheet1").Range("H3").Value, _
After:=ActiveWorkbook.Sheets("Sheet1").Range("A1"), LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Address
Range(rFound).Value = Me.TextBox1.Value
Range(rFound).Offset(0, 1).Value = Me.TextBox2.Value

Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Value = ActiveWorkbook.Sheets("Sheet1").Range("H3").Value
Me.TextBox2.Value =
ActiveWorkbook.Sheets("Sheet1").Range("A:A").Find(What:=ActiveWorkbook.Sheets("Sheet1") _
..Range("H3").Value, After:=ActiveWorkbook.Sheets("Sheet1").Range("A1"),
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Offset(0, 1)
End Sub

Then use this for your random:
Sub enterNew()
Application.EnableEvents = False
Range("H3").Formula = "=INDEX(A:A,INT(" & Range("C1").Value & "*RAND())+1)"
Range("H3").Value = Range("H3").Value
Application.EnableEvents = True
UserForm1.Show
End Sub

Now you can make changes in the Textboxes and when clicking the
commandbutton the changes get made to those cells!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Search using a combo box and then creating a report. 4
Input Box flaw? 23
Input box display 2
Error 400 in VB editor 3
Missing language pairs 0
Help With Input Box 3
Input Box labeling 2
Input Box w/Drop Down List 5

Top