I need a program to connect to excel where I can have an entry box pop up to put

Question

I need a program to connect to excel where I can have an entry box pop up to put last name, first name, middle initial, address, city, state, , zip code, and then generate a 6 digit member code which would go onto sheet one of excel. I then need a box for sheet two to generate if the member has gotten what they need for week one through five for every month in the year. This doesn't need to be but a check box which generates on sheet thre how many times it was checked. I need step by step instuctions.

Explanation / Answer

Private Sub Command1_Click()

Dim excelApp As Excel.Application
Dim excelWB As Excel.Workbook
Dim excelWS As Excel.Worksheet

Dim rowCount As Integer

Dim colCount As Integer

Dim r As Integer
Dim c As Integer

Dim foundRow As Integer

Dim foundCol As Integer

Dim foundRange As Range

Dim myRange As Range

Dim sheet As Worksheet

Set excelApp = CreateObject("Excel.Application")

excelApp.Visible = True

Set excelWB = excelApp.Workbooks.Open("C: esting estWB.xls")

Set excelWS = excelWB.Worksheets(1)

rowCount = excelWS.UsedRange.Rows.Count

colCount = excelWS.UsedRange.Columns.Count

' One way to search for things
For r = 1 To rowCount

For c = 1 To colCount

If excelWS.Cells(r, c).Value = "Using ranges with cell strings" Then

' Store the row number, and column number in a variable

foundRow = r

foundCol = c

excelWS.Cells(r, c).Value = "I found you!"

End If

Next c

Next r

' Another way

Set myRange = excelWS.Range("A2", "A10")

Set foundRange = myRange.Find("Using a loop to fill in cells", , xlFormulas, xlWhole, xlByColumns)

If Not (foundRange Is Nothing) Then

'We found it

End If

'Delete the unused worksheets

For Each sheet In excelWB.Sheets

If Not sheet.Name = "Sheet1" Then

sheet.Delete

End If

Next sheet

' Add a worksheet, then rename it

Set sheet = excelWB.Worksheets.Add

sheet.Name = "NewlyCreated"

sheet.Cells(1, 1).Value = "I'm new!"

End Sub
Private Sub Form_Load()

Dim excelApp As Excel.Application

Dim excelWB As Excel.Workbook

Dim excelWS As Excel.Worksheet

Dim rowCounter As Integer



Set excelApp = CreateObject("Excel.Application")



excelApp.Visible = True

excelApp.DisplayAlerts = False



Set excelWB = excelApp.Workbooks.Add

Set excelWS = excelWB.Worksheets(1)


'Examples of the Cells() member

excelWS.Cells(1, 1).Value = "Testing testing..."



For rowCounter = 2 To 10

excelWS.Cells(rowCounter, 1).Value = "Using a loop to fill in cells"

Next rowCounter

'Examples of the Range() member

excelWS.Range("A15", "F25").Value = "Using ranges with cell strings"

excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 4)).Value = "Using ranges with Cells() objects"


'Examples of font formatting

excelWS.Range("A15", "F25").Select

With Selection.Font

.Size = 8

.Italic = True

.Bold = True

.Underline = True

.Underline = True

.Name = "Arial Black"

End With


'Examples of number formating

excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 4)).Value = 500

excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 2)).Select

With Selection

.NumberFormat = "$#,##0.00"

End With



excelWS.Range(excelWS.Cells(1, 3), excelWS.Cells(10, 3)).Select

With Selection

.NumberFormat = "#,##0.000"

End With

excelWS.Range(excelWS.Cells(1, 4), excelWS.Cells(10, 4)).Select

With Selection

.Style = "Percent"

End With


'Save and close

excelWB.SaveAs "C: esting estWB", FileFormat:=xlExcel8

excelWB.Close

Excel.Application.Quit

Set excelApp = Nothing

End Sub

Private Sub Form_Terminate()

Dim f As Form

For Each f In Forms

If f.hWnd <> Me.hWnd Then

Unload f

Set f = Nothing

End If

Next f

Unload Me

End Sub

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.