InputBox Problem

[calihiker]

Hi,

I am using a Inputbox to ask the user where they would like to insert a line on a sheet in order to add missing info. Is there a way to let the user scroll through the sheet before they enter in the row number in the inputbox? Right now when the inputbox in up, the user can't scroll at all, is this possible with additional code or is there any alternative?

Here's the code if that helps...

Sub InputTest()
Dim r, n

Range("A14:A248&quot.Select
ActiveWorkbook.Names.Add Name:="MergePurgeKeyCodes", RefersToR1C1:= _
"='Merge Purge Report'!R14C1:R248C1"
Range("A14&quot.Select

'------------------------------------------------------
Set r = Range("MergePurgeKeyCodes&quot

For n = 1 To r.Rows.Count
If r.Cells(n, 1) <> r.Cells(n, 15) Then

Dim Message, Title, Default, MyValue
'Need to add value of cell in column A -the keycode that is missing, to the message
Message = &quot;Enter The Row #, In The Flax Sheet, That You Want To Insert A New Row Over To Include this keycode: &quot; & &quot; &quot; & r.Cells(n, 1) ' Sets prompt
Title = &quot;Found A Missing Keycode In Flax Sheet&quot; 'Sets Title
Default = &quot;1&quot; 'sets default value.
'Display message, title and default value.

MyValue = InputBox(Message, Title, Default, 100, 100)

If MyValue = &quot;&quot; Then 'exits out of function if user clicks on cancel
Exit For
End If

Worksheets(&quot;Test Sheet&quot.Rows(MyValue).Insert 'change sheet name later to real name

End If
Next n


End Sub

Any suggestions would be very appreciated, thanks.
Andre

 

[mep1]

Perhaps you should let the user scroll through the spreadsheet and find the row, select the row, then fire off your macro with a hotkey.

Instead of specifying what row to insert via an inputbox, your macro will recognize the currently selected row and insert that way?

Just a thought...

MEP

 

[calihiker]

Thanks! I'll do that, it'll work just as good.
But I would like to know if it is possible to scroll while a inputbox or messagebox is up. If anyone knows, Please let me know -thanks.

 

[Zathras]

If you have Excel 2K or later, you can display the user form &quot;modeless&quot; which will allow the user to work in the spreadsheet while the form is showing.

If you have Excel 97, you should be able to put buttons on the form that would execute code to change which rows are visible under the form. I've never tried it however.

 

[calihiker]

Thanks Zathras!

I'll try that. I have XP, but when I look up 'Modeless form' on the excel on-line help, I found nothing. Does the Excel help files have anything on it or can I read up on it somewhere else? Or do you have any code that I can use? Thanks.

 

[mep1]

Was thinking of the modeless form as well...

calihiker, I believe it is simply a property of the formitself...

myForm.showmodal = false[\code]

-MEP

 

[Zathras]

In Excel 2K it is a parameter with the .Show method. Something like

   myForm.Show vbModeless

 

[calihiker]

Thanks!

 

[calihiker]

Ok, that worked But as I am fairly new to Excel VBA - how would I go about getting data from the a text box into a variable and then using that variable in a way to insert a line above a certain row in the worksheet? Would it be something like this below? BTW, this isn't working :-( I get an &quot;application-defined or object-defined&quot; error...


Sub UserFormTest()
Dim RowValue

UserForm007.Show vbModeless

RowValue = Val(UserForm007.txtRowNumber.Value)

Worksheets(&quot;Test Sheet&quot.Rows(RowValue).Insert

End Sub

 

[Zathras]

Well, first of all after you show the form you need to leave it up to the user to do something. So, delete everything after the UserForm007.Show and before the End Sub.

Then, put code in the appropriate event handler for which ever method you want to give to the user for doing his thing.

For example, mep1 is on the right track if you want to allow the user to select the row with the mouse and double-click, put code in the Worksheet_BeforeDoubleClick() event handler.

To follow on with your original idea, have a command button on the form and put code in the CommandButton1_Click() event handler.

You could have it both ways by calling the same public sub from both event handlers.

In the sub itself, you can test the ActiveSheet.Selection range to see which cell/range is selected. (And you can assign a hot key to the sub.)

Sort of like this:

[blue]Worksheet code page:[/color]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
  Call doit
End Sub

[blue]Form code page:[/color]

Private Sub CommandButton1_Click()
  Call doit
End Sub

[blue]Module code:[/color]

Sub doit()
  MsgBox Selection.Address
End Sub

The possibilities are endless.

 

[calihiker]

Ok, I've incorporated the userform and have the user selecting the 'ok' button after the enter in a number in the text box. The problem is that the form doesn't pop up when the if criteria is true.. I tried stepping through the code to make sure that it went inside the if statement when the two columns didn't match and it did...and it seemed like the form went up -but only for like a millisecond??? and then it disappeared before i could do anything??? Help please, thanks.

Here's the code...

Sub UserFormTest()

Dim r, n

Range(&quot;A14:A248&quot.Select
ActiveWorkbook.Names.Add Name:=&quot;MergePurgeKeyCodes&quot;, RefersToR1C1:= _
&quot;='Merge Purge Report'!R14C1:R248C1&quot;
Range(&quot;A14&quot.Select

Set r = Range(&quot;MergePurgeKeyCodes&quot

For n = 1 To r.Rows.Count
If r.Cells(n, 1) <> r.Cells(n, 15) Then

With UserForm007.lblPrompt
.Caption = &quot;Enter The Row # of The Flax Sheet, That You Want To Insert A New Row Over To Include this keycode: &quot; & r.Cells(n, 1)
End With
UserForm007.Show vbModeless
End If
Next n

End Sub

 

[Zathras]

Please re-read the first pararagraph of my last post. You are continuing to do stuff inside your macro after the line:

   UserForm007.Show vbModeless

It's not nice to display a form for the user to work with and then start doing stuff in that form yourself. (Which is what is happening as you continue looping.)

If you want to keep control, then you must not use

 vbModeless

--- Show the form modal. That will stop the macro execution and wait for the user to close the form. Since you want the user to be able to browse the underlying worksheet, you will have to code that functionality in the form. A vertical scroll bar on the form should be easily understood by the user but you will have to do a bit of coding in order for it to behave correctly.