change from DialogSheets to Userform

[Office User]

Trying to update an old Excel 95 template in Excel 2000 version. Part of the
code is dealing with DialogSheets which, I found out, were replaced by
userforms. When this ran in 95, the caption of the dialog box would change.
Essentially the same dialog box was used but the controls changed based on
the status.

Does anyone have a suggestion for changing this to userforms? Do I need to
create 2 different userforms and then somehow code it dependent on that?

Below is the code:


Sub LockSheet()
'Controls the Lock Sheet button on the Customize page
Const LockDlg = "Lock"
Const Lock_String = "Lock/Save Sheet"
Const Lock_Text = "You can lock the information on the Customize page
and save your customized version of the template."
Const Unlock_String = "Unlock This Sheet"
Const Unlock_Text = "By unlocking this sheet, you enable changes to be
made to the information on the Customize sheet. Select ""Lock This Sheet""
after you make your changes to protect the sheet from accidental changes."
Const Save_Alrt = "Your new customized template has been saved to the
default directory. "
Const Save_Alrt2 = "To begin using the invoice, double-click the
shortcut in Pioneer Applications folder."
Const Save_Filter = "Templates,*.xlt"
Const Save_Title = "Save Template"


If Sheets(Vital).DrawingObjects("Lock").Caption = Lock_String Then

If DialogSheets(LockDlg).Show Then
Sheets(Vital).Protect DrawingObjects:=True, Contents:=True
Sheets(Vital).DrawingObjects("Lock").Caption = Unlock_String
Sheets(LockDlg).DialogFrame.Caption = Unlock_String
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Text = Unlock_Text
Sheets(LockDlg).GroupBoxes("PNL2").Visible = False
Sheets(LockDlg).OptionButtons("LCK_1").Visible = False
Sheets(LockDlg).OptionButtons("LCK_2").Visible = False
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Height = 80
If Sheets(LockDlg).OptionButtons("LCK_2").Value = xlOn Then
ThisDir = CurDir()
TempDir = Application.TemplatesPath
ChDrive Mid(TempDir, 1, 1)
ChDir TempDir
FileNm = Application.GetSaveAsFilename(FileFilter:=Save_Filter,
Title:=Save_Title)
If FileNm <> False Then
OWFlg = Application.DisplayAlerts
Application.DisplayAlerts = False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Content1).Activate
Sheets(Vital).Visible = False
With ActiveWorkbook
.SaveAs Filename:=FileNm, FileFormat:=xlTemplate
FName = .FullName
PName = .Path
End With
Application.DisplayAlerts = OWFlg
MsgBox Save_Alrt & PName & Save_Alrt2, vbOKOnly + vbInformation,
SheetBar
ThisWorkbook.Close
End If
ChDrive Mid(ThisDir, 1, 1)
ChDir ThisDir
End If
End If

Else

If DialogSheets(LockDlg).Show Then
Sheets(Vital).Unprotect
Sheets(Vital).DrawingObjects("Lock").Caption = Lock_String
Sheets(LockDlg).DialogFrame.Caption = Lock_String
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Text = Lock_Text
Sheets(LockDlg).GroupBoxes("PNL2").Visible = True
Sheets(LockDlg).OptionButtons("LCK_1").Visible = True
Sheets(LockDlg).OptionButtons("LCK_2").Visible = True
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Height = 40
End If

End If

End Sub


Thanks for any input.
Marcia

 

[Bob Phillips]

Haven't looked too deeply at your code, but you could have a two page
multipage control on the userform, and flip-flop between the two.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Dialog sheets can still be up to and including the latest version of excel.

 

[Office User]

The template I'm using must have a hidden form/sheet because the code won't
work. I'm in the midst of creating a new userform (actually 2) and coding
accordingly.

Thanks for the input,
Marcia

 

[Harald Staff]

Hi Marcia

Try
http://j-walk.com/ss/excel/files/dlgwiz.htm

But nothing from older versions "don't work" in newer versions. So the form
conversion may not fix your problem, I believe it it something else.

HTH. Best wishes Harald

 

[Tom Ogilvy]

Just to highlight:

So all the functionality would have to be recreated.

Thus my suggestion that dialogsheets work fine in later versions of Excel.