VB Script To Prompt For Cell Reference

Microsoft Excel 2000/visual basic for ap...
August 17, 2009 at 11:41:59
Specs: Windows XP PRO SP2, P4 - 2.66GHz / 2 Gigs DDR
Hello,

I have this script:
'=================================
Private Sub CommandButton1_Click()
'Randomizing Lists

MsgBox ("Before running this script, you will need:" & Chr(13) & _
"The name of a cell that has data throughout the list as a reference point (i.e. Name field)" & Chr(13) & _
"The name of the cell that you want the random numbers to be added to (i.e. column to the right of last column)" & Chr(13))


Dim varFile As Variant
Dim lngCount As Long
Dim RecCount As Long
Dim BadNumber As Long

varFile = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Open Sample File", , False)
If TypeName(varFile) = "Boolean" Then 'the user didn't select a file
Exit Sub
End If
Application.ScreenUpdating = False
Workbooks.Open Filename:=varFile
'MAKE SURE TO IDENTIFY A CELL THAT HAS DATA IN IT THROUGHOUT THE SAMPLE AS A REFERENCE POINT
Range("D1").Select
Do Until ActiveCell.Value = Empty
ActiveCell.Offset(1, 0).Select
RecCount = RecCount + 1
Loop


'************************************************************
'RANDOMIZING THE SAMPLE *
'************************************************************
Dim intCumber As Integer
Dim Highest As Integer
Dim Lowest As Integer
Highest = 10000
Lowest = 1
Randomize
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
'MAKE SURE TO IDENTIFY A CELL THAT HAS DATA IN IT THROUGHOUT THE SAMPLE AS A REFERENCE POINT
Range("D2").Select
Do Until ActiveCell.Value = Empty
ActiveCell.Offset(0, 29).Value = RandomNumber
ActiveCell.Offset(1, 0).Select
Randomize
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
Loop
Cells.Select
Selection.Sort Key1:=Range("ag2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("ah:ah").Select
Selection.Delete Shift:=xlToLeft



'************************
'SAVE AND CLOSE PROGRAM *
'************************
'after the "&" sign you can add whatever you want to the file name to indicate that it is cleaned
'command below is to save current file taking off the ".xls" (-4 below)
ActiveWorkbook.SaveAs Mid(varFile, 1, Len(varFile) - 4) & "_Randomized.xls"
ActiveWorkbook.Close 'To close the sample file workbook

'*****************
'DISPLAY SUMMARY *
'*****************
MsgBox ("Summary Report" & Chr(13) & _
"Total Number Of Records: " & RecCount & Chr(13) & _
"Total Number Of Bad Records: " & BadNumber & Chr(13) & _
"Total Left Clean Sample: " & RecCount - BadNumber)
ActiveWorkbook.Save
Application.Quit

End Sub

Private Sub CommandButton2_Click()
Application.Quit
End Sub

Private Sub UserForm_Initialize()
'This is to check to make sure there is no other open workbooks
Dim i As Integer
i = Workbooks.Count ' count of open workbooks
If i > 1 Then
MsgBox ("Warning! To run this program please exit out of all excel files open and try again.")
End
End If
End Sub
'=================================
I am wondering if anyone knows of a command to pop an input box asking for the cell that will be used to store the random numbers?

Thanks,
Chad


See More: VB Script To Prompt For Cell Reference

Report •


#1
August 18, 2009 at 06:22:30
You should probably have mentioned you were working with VBA, not a "VB Script;" people will assume you meant VBScript, which is a different language.

This scenario is typically handled one of three ways.
1) Have them select the cell before activating the script.
2) Use a generic InputBox
3) Make a UserForm with a RefEdit control.

The method you select will depend on the target audience and the level of polish you choose to present.


Report •
Related Solutions


Ask Question