PDA

View Full Version : Solved: Generating numbers in excel



Spratt_Cary
08-31-2007, 08:19 PM
Attached spreadsheet. I was wanting to know if there's a better way to generate specific numbers for each worksheet. There are 3 functions for this userform. 1) Running log of information. 2) To create a data change word document(linking) that goes to the programmer (Generate new number) and 3) to generate a Telephone call (No number needed). As you can see, I am basically copy/paste/delete cell. I am not a programmer of VBA, so I am asking the experts, any help will be greatly appreciated.

Bob Phillips
09-01-2007, 01:36 AM
So what are you asking to do? Randomly select a value from the dropdown?

Spratt_Cary
09-01-2007, 05:17 AM
Sorry for being vague, I was wondering if there was a better way to generate the number for each sheet. Sheet1(1829A) will have a range of 500-3000, Sheet2(1829B) will have a range of 3001-5000, Sheet2(2321) will have a range of 5001-7000. These number will be assigned if the end user selects the radio button "New Number". When this number is assigned, the number is placed in A2 cell. There are 2 functions to this userform, to store an on going log of changes and telephone calls and links to a word template that can be printed out. The telephone calls do not assign a number. So in the column A, there are assigned numbers for changes and blanks for telephone logs, depending on what function the end user inputs. So therefore the 4th sheet called "Numbers" where I am doing a copy/paste/delete. This seems really messy and "Unprofessional" looking. So hense asking the experts if there's a better way to perform this assigning of numbers. thanks for the question and I hope this helps.

mdmackillop
09-01-2007, 10:24 AM
'Use Change event below instead
Private Sub xxxCmbStudy_DropButtonClick()
'Selects the appropriate study sheet.
If CmbStudy = "1829A" Then Sheets("1829A").Select
If CmbStudy = "1829B" Then Sheets("1829B").Select
If CmbStudy = "2321" Then Sheets("2321").Select
End Sub

Private Sub CmbStudy_Change()
Sheets(CmbStudy.Text).Activate
OptionButton1 = False
End Sub

Private Sub OptionButton1_Click()
'Prevent screen flipping, Ensures end user select the correct sheet, deactivitates the 2nd option button fields,_
'selects the Numbers sheet and cut/copy number appropriate for study sheet.
Dim NewNo As Long
Application.ScreenUpdating = False
If CmbStudy = "" Then
Const file1 = "Click OK and select a Study Number"
Dim Response
Response = MsgBox(file1, vbOKOnly + vbQuestion, "Study Number Needed")
If Response = vbOKOnly Then Application.DisplayAlerts = False
OptionButton1 = False
End If

If OptionButton1 = True Then
CommandButton2.Enabled = False
OButton2.Enabled = False
TBTeleCon.Enabled = False
End If

Sheets(CmbStudy.Text).Activate
Rows(2).Insert Shift:=xlDown
NewNo = Application.Max(Columns(1)) + 1
If NewNo = 1 Then

Select Case CmbStudy.Text
Case "1829A"
NewNo = 500
Case "1829B"
NewNo = 3001
Case "2321"
NewNo = 5001
End Select
End If

Range("A2") = NewNo
Range("I2") = Now
Range("J2").Value = Environ("username")
If CmbStudy = "2321" Then
Range("K2").Value = ("Alcon C-05-39 2321 Study")
End If
Application.ScreenUpdating = True
End Sub

Spratt_Cary
09-01-2007, 11:27 AM
Perfect. Thank you mdmackillop.