Cell Offset User Input

[ajvasel]

I am familiar with using cell offset to move the active cell to a
non-specific cell location in VB; however, I am wondering how difficult it
would be to build a user interface that would prompt the user to input the
number of rows/columns to move up/down left/right. I have used user-input
pop-up boxes before but only with a limited number of variables that could be
entered. Any help (or at least a start in the right direction) would be
greatly appreciated. Thanks

 

[Joel]

Have you every used the userforms in VBA?
Start by going to VBA Window and from Insert Menu, select userform. Click
on Userform and select Hammer and Wrench button on toolbar to get the
toolbox. You can add RefEdit to accept text inputs. Can have more than one
Reffedit on the userform.

 

[JLGWhiz]

You can use the InputBox function or InputBox Method to have the user enter a
cell location that they want to move to. The problem with these two
approaches is that you have to depend on the user to properly enter the data.
You can also write code with the input boxes that would allow the user to
enter an integer number for moving the cusor, but the direction of the move
would have to be predetermined unless you want to write a select case or
If...ElseIf... statement to work in conjunction with message box to allow the
user to make choices that the code can use their response to manipulate the
cursor.

To make this long story short, yes there are several ways to interface with
the user in selecting cells on a worksheet via VBA. It just depends on how
much code or UserForm controls the code writer wants to utilize.

 

[ajvasel]

thanks for the suggestions - I will try them when I have some spare time
tomorrow

 

[ajvasel]

This is what I came up with - I am not sure how I can build stronger controls
into it using If Then statements. Any thoughts...

Option Explicit

Sub MoveCell()
Dim rslt1 As Integer
Enternumber:
rslt1 = Application.InputBox("Enter the number of cells to space
down - use a negative number to space up")
Dim rslt2 As Integer
Enternumber2:
rslt2 = Application.InputBox("Enter the number of cells to space
right - use a negative number to space right")
ActiveCell.Offset([rslt1], [rslt2]).Activate
End Sub

 

[Susan]

well.............
refedit boxes are notoriously prone to problems (i've read), but i
think this would be a good place to try one..........
the problem (i see) with what you're doing is that unless the user
knows EXACTLY where you are starting from, how are they going to know
which direction (& how many movements) they need to go?
but i don't know the rest of your code so maybe it's not an issue.

another thought - if they know where they're starting FROM, why not
just an input box that lets them input the address, i.e., A4 or AC34?

i don't know what you mean about building IF statements into it,
because i don't see where you are giving them a choice about
anything......

just an idea
susan