pop-up for Values

[NotSoVisual]

I am new to Excel, but do know much more VB6 and know nothing of VBA. Could someone point me in the right direction to solve the following.

Columns B,C and D (all 3 to be hidden) are numeric (0 places) and to contain M,D,Y (Month, Day, Year) in the range of 3000 BC to the future.. Upon completion of input to column A or upon the click of a button I want to present user with a request for input m,d,y and J or G decision via option buttons. So I could store the data in the hidden columns.

Just a hint or two to get me started. I will be glad to share the sheet if required.

 

[VBAjedi]

Lots of ways to do this. . . you could just use popup boxes:

CurRow = 4 ' Set by your code to whatever row the user is working on. . .
Worksheets("Sheet1").Range("B" & CurRow).Value = Inputbox("Please enter 2-digit month")
Worksheets("Sheet1").Range("C" & CurRow).Value = Inputbox("Please enter 2-digit day")
Worksheets("Sheet1").Range("D" & CurRow).Value = Inputbox("Please enter 4-digit year")

Or you could build a simple form to get all the values (this will be the better way to go if you really want those option buttons). Put an "Ok" button on it, and use that buttons' Click event to write the values to the worksheet and close the form.


VBAjedi

 

[NotSoVisual]

Thanks. I need some more help. I am afraid for you that I am a rank beginner at this and at age 67 there is not much time to waste. I would be willing to share my worksheet publically or privately. It may be of some use to some out there for it works manually w/o the whistles and is accurate in so far as it works with dates from 3113 BC to the future insofar as it converts any of those dates to a sequential julian day number. With the deletions of some rows it will handle dates from 1/1/4712BC onward.
I have gotten to write a form as you suggested and have y,m,d input boxes as well as 2 option buttons in a frame. I could possibly get this thing done IF I:
a: could determine the "active row"
b: Find out what it takes to Pop-up the form
c: Find out what it takes to Close the form & return to the worksheet.

 

[NotSoVisual]

Thanks. I need some more help. I am afraid for you that I am a rank beginner at this and at age 67 there is not much time to waste. I would be willing to share my worksheet publically or privately. It may be of some use to some out there for it works manually w/o the whistles and is accurate in so far as it works with dates from 3113 BC to the future insofar as it converts any of those dates to a sequential julian day number. With the deletions of some rows it will handle dates from 1/1/4712BC onward.
I have gotten to write a form as you suggested and have y,m,d input boxes as well as 2 option buttons in a frame. I could possibly get this thing done IF I:
a: could determine the "active row"
b: Find out what it takes to Pop-up the form
c: Find out what it takes to Close the form & return to the worksheet.
A question.. If I had user "Click the row number to Populate" and the user did click row x:
How could I activate the FrMDateIN?
How could I determine x or is it a "system variable" etc.

 

[mrmovie]

MsgBox ActiveCell.Row

 

[DrBowes]

Try putting this in the worksheet module to make it run when column a is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
CurRow = target.row ' Set by your code to whatever row the 'user is working on. . .
Worksheets("Sheet1").Range("B" & CurRow).Value = Inputbox("Please enter 2-digit month")
Worksheets("Sheet1").Range("C" & CurRow).Value = Inputbox("Please enter 2-digit day")
Worksheets("Sheet1").Range("D" & CurRow).Value = Inputbox("Please enter 4-digit year")
End If
End Sub

You could also use activecell.row to get the currently selected row if you are running the code with a button.

To close a form add a command button and use:

Private Sub CommandButton1_Click()
End
End Sub

To activate on a click use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
currow = target.row
FrMDateIN.show
End Sub

 

[NotSoVisual]

Thank you folks. I finally got this little experiment to work to a degree that 'satisfies' me. I have been working with the problems of MS dates for a long time now and the limits imposed by MS products of 1900 to 9999 just have not been helpful to me since my interest is in historical events (in particular Maya calendar dates and conversion to ours) I've always done it through Julian Day Numbers and various editions of Basic (from DOS to VB and MSpro basic 7.1). Now you helped me do it in Excel. Thanks. If you are interested I will share same with you.
Thanks again. I need to find a book on VBA.

 

[VBAjedi]

Just FYI: John Walkenbach (known as J-Walk) has some great books out on VBA. Try Googling "Walkenbach VBA book". . .

VBAjedi