Dialog Boxes

[nick1408]

Hello all.

I have a workbook with numerous sheets. What I would like is to write a macro that will do the following (in order):

- Copy sheet 1
- Dialog Box to rename new sheet
- Select cell A2
- Dialog Box "Input Date"
- Select cell A3
- Dialog Box "Input 2nd Date"
- Select cell C2
- Dialog Box "Name"
- Select cell E2
- Dialog Box "Input 2nd Name"
- Select cell C3
- Dialog Box "Ranking"
- Select cell E3
- Dialog Box "Input 2nd Ranking"
- Select cell C6
- Dialog Box "Weight 1"
- Select cell E6
- Dialog Box "Weight 2"
- Select cell C7
- Dialog Box "Stride 1"
- Select cell E7
- Dialog Box "Stride 2"
- Select cell C8
- Dialog Box "Med 1"
- Select cell E8
- Dialog Box "Med 2"
- Select cell C9
- Dialog Box "Phys 1"
- Select cell E9
- Dialog Box "Phys 2"
- Select cell C10
- Dialog Box "Vet 1"
- Select cell E10
- Dialog Box "Vet 2"
- Select cell C11
- Dialog Box "Gate 1"
- Select cell E11
- Dialog Box "Gate 2"
- Select cell C12
- Dialog Box "Accell 1"
- Select cell E12
- Dialog Box "Accell 2"
- Select cell C13
- Dialog Box "Muscle 1"
- Select cell E13
- Dialog Box "Muscle 2"
- Select cell C14
- Dialog Box "Stam 1"
- Select cell E14
- Dialog Box "Stam 2"
- Select cell C15
- Dialog Box "Heart 1"
- Select cell E15
- Dialog Box "Heart 2"
- Select cell D11
- Dialog Box "Gen Gate 1"
- Select cell F11
- Dialog Box "Gen Gate 2"
- Select cell D12
- Dialog Box "Gen Accell 1"
- Select cell F12
- Dialog Box "Gen Accell 2"
- Select cell D13
- Dialog Box "Gen Muscle 1"
- Select cell F13
- Dialog Box "Gen Muscle 2"
- Select cell D14
- Dialog Box "Gen Stam 1"
- Select cell F14
- Dialog Box "Gen Stam 2"
- Select cell D15
- Dialog Box "Gen Heart 1"
- Select cell F15
- Dialog Box "Gen Heart 2"
- Select cell N5
- Dialog Box "Starting Rating"

Thanks for the help.

 

[stefankemp]

Does it have to select the cells each time? Why not do one userform with text boxes for all the inputs required. Basically a form that needs to be filled out. You can then update all the cells in one go when the user clicks OK.

 

[nick1408]

I guess it doesn't but I would prefer dialog boxes

 

[stefankemp]

So do you want them to appear one after the other? For example dialog box for new sheet name, user enters it then press enter. Then the next dialog box appears for 1st date, user enters it then press enter. And so forth.

 

[nick1408]

Exactly.

 

[stefankemp]

OK, I would add a sheet somewhere and enter all the prompts for the dialogs in a range, say "A1:A30" (however many there are). And in the next column enter the cell that it relates to. For example, A4 = "Name", B4 = "C2". Name the new sheet "Dialogs" (or whatever).

Then, assuming you want to copy sheet1 to a new workbook:

sub TryThis()

dim cell as range
dim rngPrompts as range
dim rngAddresses as range
dim strInput as string 

set rngPrompts = sheets("Dialogs").range("A1:A30")
set rngAddresses = sheets("Dialogs").range("B1:B30")

worksheets(1).copy
activesheet.name = inputbox(prompt:="New sheet name")

for each cell in rngPrompts
  strInput = inputbox(prompt:=cell.value)
  activesheet.range(cell.offset(0,1)).value = strInput
next cell

end sub

 

[nick1408]

Perfect. Thank you very much

 

[Norie]

Nick1408

Are userforms a definite no-go?

With dialog boxes the user isn't going to have much chance to fix any mistakes they might make when entering data.

If all input was a via a userform then it could be validated as it's entered or before it gets comitted to a worksheet.

 

[nick1408]

Nah, not a no-go. I haven't worked with userforms before. I do see their potential but in my case I feel dialog boxes work better.

 

[Norie]

Seriously, 35 input boxes? (I think it's about that many - I gave up counting about Stam 2).

A userform might take a bit of work to set up but it would be a lot easier to maintain and generally work for things like validation.